Making new Columns from data in an existing one

John_mac

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

txnelson

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#++;
);