Choose Language Hide Translation Bar
John_mac
New Contributor

Making new Columns from data in an existing one

I have a large dataset with one of the columns of data containing cells with the format below. Is there a way to pull out the different ‘AppNames’ into 3 separate columns. I’ve tried the Text to Columns function in Utilities, but not all cells in the column have more than one appname listed or they may have slightly different information included and that throws out the alignment and I end up getting incorrect values in my new columns…Ideally, can I use the 'AppName:' string as a 'delimiter'? So from the example below...I should end up with three new columns - with data refering to Application1, Application2 and Application3...thank you

 

Format

tx:AppID:32734, tx:AppName:Application1, tx:ServerEnv:Development, tx:ServerStatus:Deployed, tx:OwningVP:xxxxxxxxx, tx:DevMgr:xxxxxxxxx, tx:OwningDirector:xxxxxxxxx, tx:AppOwner:xxxxxxxxx, tx:BusinessOwner:xxxxxxxxx, tx:SOX:No, tx:PCI:No, tx:L5_MGR:xxxxxxxxx, tx:DataClass:Internal Use, tx:DRClass:Class I, tx:AppType:IT Supported, tx:OrgL2:TSO, tx:OrgL3:TMX, tx:OrgL4:Mobile and VC, tx:AppID:655369, tx:AppName:Application2, tx:OwningVP:xxxxxxxx, tx:DevMgr:xxxxxxxx, tx:OwningDirector:xxxxxxxx, tx:AppOwner:xxxxxxxx, tx:BusinessOwner:xxxxxxxxx, tx:SOX:KFS Tier 2, tx::KBA Tier 1, tx:DataClass:Restricted, tx:OrgL2:CBS, tx:OrgL3:, tx: tx:AppID:655364, tx:AppName:Application3

0 Kudos
4 REPLIES 4
txnelson
Super User

Re: Making new Columns from data in an existing one

I took a little different approach to your problem.  In looking at your data, I realized that what you have are a lot of different columns, with the rows being the applications.  So I have written a little script that breaks all of this out.  Now the issue is, there are errors in the data.  So I have added an area within the code that attempts to fix them.  Not having all of your data, I am sure there may be more fixes that need to be put in.  Once these data are in, if you still want your data to be aligned into 3 columns, you can easily do a transpose of the data into the 3 columns.

Names Default To Here( 1 );

theData =
"tx:AppID:32734, tx:AppName:Application1, tx:ServerEnv:Development, tx:ServerStatus:Deployed, tx:OwningVP:xxxxxxxxx, tx:DevMgr:xxxxxxxxx, tx:OwningDirector:xxxxxxxxx, tx:AppOwner:xxxxxxxxx, tx:BusinessOwner:xxxxxxxxx, tx:SOX:No, tx:PCI:No, tx:L5_MGR:xxxxxxxxx, tx:DataClass:Internal Use, tx:DRClass:Class I, tx:AppType:IT Supported, tx:OrgL2:TSO, tx:OrgL3:TMX, tx:OrgL4:Mobile and VC, tx:AppID:655369, tx:AppName:Application2, tx:OwningVP:xxxxxxxx, tx:DevMgr:xxxxxxxx, tx:OwningDirector:xxxxxxxx, tx:AppOwner:xxxxxxxx, tx:BusinessOwner:xxxxxxxxx, tx:SOX:KFS Tier 2, tx::KBA Tier 1, tx:DataClass:Restricted, tx:OrgL2:CBS, tx:OrgL3:, tx: tx:AppID:655364, tx:AppName:Application3";

dt = New Table( "The Table" );
dt << delete columns( "Column 1" );

word# = 1;
While( Word( word#, theData, "," ) != "",
	colNameList = dt << get column names( string );
	theWord = trim(Word( word#, theData, "," ));
	
	// Make found corrections
	If( Trim( Word( 2, theWord, ":" ) ) == "tx",
		theword = Word( 2, theword, ":" ) || ":" || Word( 3, theWord, ":" ) || ":" || Word( 4, theWord, ":" )
	);
	If( word( 1, theWord, ":") == "", theWord = "tx" || theWord );
	
	if( word( 2, theWord, ":") == "",
		theword = Word( 1, theword, ":" ) || ":" || word(1,Word( 3, theWord, " " )," ") || ":" || 
		substr(Word( 3, theWord, ":" ),length(Word( 3, theWord, ":" ))+2);
	);
	if( word( 3, theWord, ":") == "",
		theword = Word( 1, theword, ":" ) || ":" || word(1,Word( 2, theWord, ":" )," ") || ":" || 
		substr(Word( 2, theWord, ":" ),length(Word( 1, Word( 2, theWord, ":" ), " " ))+2);
	);
	
	if(word(2,theword,":")=="KBA",show(theword));		
	colName = Word( 2, theWord, ":" );
	If( Word( 2, theWord, ":" ) == "AppID",
		dt << Add Rows( 1 );
	);
	If( N Rows( Loc( colNameList, colName ) ) == 0,
		dt << New Column( colName, character );
	);
	Column( dt, colName )[N Rows( dt )] = Word( 3, theWord, ":" );
	word#++;
);
Jim
John_mac
New Contributor

Re: Making new Columns from data in an existing one

Thank you Jim...I guess what I'm trying to do is pull out the 'AppName' data from column 'Asset Tag' and get them into new columns (Application #1, #2, #3). See screenshot below. 

(AppName:Exchange 2013 (350578) from Asset Tags in Row 1 below)

 

Some of the rows in column 'Asset Tags' have more than one AppName included and unfortuntately, they are not uniform, see examples below:

 

Example 1 format (1 AppName)
tx:AppID:350578, tx:AppName:Exchange 2013 (350578), tx:ServerEnv:Production, tx:ServerStatus:In Maintenance, tx:OwningVP:xxxxxxxxx, tx:DevMgr:xxxxxxxxx, tx:OwningDirector:xxxxxxxxx, tx:AppOwner:xxxxxxxxx, tx:BusinessOwner:xxxxxxxxx, tx:SOX:No, tx:PCI:No, tx:L5_MGR:xxxxxxxxx, tx:EMEABanking:KBA Tier 2, tx:DataClass:Highly Restricted, tx:DRClass:Class I, tx:AppType:IT Supported, tx:OrgL2:TSO, tx:OrgL3:TMX, tx:OrgL4:Messaging and Content Mgmt

 

Example 2 format (3 AppNames)
tx:AppID:32734, tx:AppName:CITRIX (32734), tx:ServerEnv:Development, tx:ServerStatus:Deployed, tx:OwningVP:XXXXXXXXX, tx:DevMgr:XXXXXXXXX, tx:OwningDirector:XXXXXXXXX, tx:AppOwner:XXXXXXXXX, tx:BusinessOwner:XXXXXXXXX, tx:SOX:No, tx:PCI:No, tx:L5_MGR:XXXXXXXXX, tx:EMEABanking:KBA Tier 2, tx:DataClass:Internal Use, tx:DRClass:Class I, tx:AppType:IT Supported, tx:OrgL2:TSO, tx:OrgL3:TMX, tx:OrgL4:Mobile and VC, tx:AppID:655369, tx:AppName:EBOSS EMEA (655369), tx:OwningVP: XXXXXXXXX, tx:DevMgr: XXXXXXXXX, tx:OwningDirector: XXXXXXXXX, tx:AppOwner: XXXXXXXXX, tx:BusinessOwner: XXXXXXXXX, tx:SOX:KFS Tier 2, tx:L5_MGR: XXXXXXXXX, tx:EMEABanking:KBA Tier 1, tx:DataClass:Restricted, tx:OrgL2:CBS, tx:OrgL3:Dell Financial SVCS, tx:OrgL4:DFS Originations and Lease Servicing, tx:AppID:655364, tx:AppName:TVALUE (655364)

 

Example 3 format (1 AppName, but different format)
ext:AppName:Voltage, ext:PCI:N, ext:OrgL3:TMX, ext:L5_Mgr:xxxxxxxxx, ext:Owning_Director: xxxxxxxxx, ext:App_Owner: xxxxxxxxx, ext:Owning_Group:Messaging and Content Mgmt, Dell-External, External, High, Prohibited_Services, DC Scan - External, tx:AppID:2812, tx:AppName:Exchange 2010, tx:ServerEnv:Production, tx:ServerStatus:Deployed, tx:OwningVP: xxxxxxxxx, tx:DevMgr: xxxxxxxxx, tx:OwningDirector: xxxxxxxxx, tx:AppOwner: xxxxxxxxx, tx:BusinessOwner: xxxxxxxxx, tx:SOX:No, tx:PCI:No, tx:L5_MGR: xxxxxxxxx, tx:EMEABanking:No, tx:DataClass:Highly Restricted, tx:DRClass:Class I, tx:AppType:IT Supported, tx:OrgL2:TSO, tx:OrgL3:TMX, tx:OrgL4:Messaging and Content Mgmt

 

Note: Application #1 column below was created by using the Text-to-Column function in Col>Utilities...

 

Not sure if explaining this well ;-(

jmp.jpg

0 Kudos
txnelson
Super User

Re: Making new Columns from data in an existing one

I believe this code will do what you want.  Attached is a data table, with the 3 rows of the examples you provided.  The script below handles the multiple application extractions

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "Application #1", character );
dt << New Column( "Application #2", character );
dt << New Column( "Application #3", character );

For( theRow = 1, theRow <= N Rows( dt ), theRow++,
	word# = 1;
	appName# = 1;
	While( Word( word#, :Asset Tags[theRow], "," ) != "",
		theWord = Trim( Word( word#, :Asset Tags[theRow], "," ) );
		If( Word( 2, theWord, ":" ) == "AppName",
			Column( dt, "Application #" || Char( appName# ) )[theRow] = Word( 3, theWord, ":" );
			appName#++;
		);
		word#++;
	);
);
Jim
0 Kudos
pmroz
Super User

Re: Making new Columns from data in an existing one

Here's a slightly different approach that includes adding the values to the table.  I have a parsing function at the top of the code that is similar to words, but the delimiters can be multi-character.

//------------------------------------------------------------------------------
/*
Function Name: my_parse

Description:    Parse text according to a given delimiter.  Two delimiters in a row
				will yield an empty list entry, which is different from the behavior 
				of WORDS
				
Returns: list of tokens

Arguments:	
_text		Text to determine be parsed
_delim		Delimiter
*/
my_parse = function({_text, _delim},
	{Default Local},

	pname = "get_words";

	if (is missing(_text),
	// then
		ret_list = {};
		,
	// else if
		_text == "",
		ret_list = {};
		,
	// else we've got some text to work with
		ret_list = {};
		len_delim = length(_delim);
		istart = 1;
		keep_going = 1;
		while (keep_going,
			iend = contains(_text, _delim, istart);
			if (!iend,
				keep_going = 0;
				insertinto(ret_list, substr(_text, istart), 1);
				,
				insertinto(ret_list, substr(_text, istart, (iend - istart)), 1);
				istart = iend + len_delim;
			);
		);
	);

	reverse(ret_list);
);	// end my_parse

//------------------------------------------------------------------------------
dt = current data table();

dt << new column("Application #1", character);
dt << new column("Application #2", character);
dt << new column("Application #3", character);

for(i = 1, i <= nrows(dt), i++,
	
	one_asset = dt:asset tags[i];

	one_list = my_parse(one_asset, ", ");
	app_list = {};
	m = 0;
	for (k = 1, k <= nitems(one_list), k++,
		one_item = one_list[k];
		aloc = contains(one_item, "AppName:");
		if (aloc,
			m++;
			app_list[m] = substr(one_item, 8 + aloc);
		);
	);
	show(app_list);
// Now add these to the respective Application # columns
	for (k = 1, k <= nitems(app_list), k++,
		cname = "Application #" || char(k);
		column(dt, cname)[i] = app_list[k];
	);
);
0 Kudos