topic Re: Need to help to use Split function in Discussions

Need to help to use Split function

raj138 — Wed, 24 Jul 2019 21:33:11 GMT

Can you somebody provide instructions to modify the attached original table to get to the final table. I assume I need to use Split function and then manually move the coloumns around, but the table I have on hand has multiple rows and coloumns so manually moving around is not an option. Any tricks that can be done here?

Re: Need to help to use Split function

txnelson — Thu, 25 Jul 2019 10:59:42 GMT

Here is a simple script that I would use to create the table you want.

Names Default To Here( 1 );
dt = Current Data Table();
colNames = dt << get column names( string );
Summarize( dt, fruitGroups = by( :Fruit ) );

dt << select where( :Fruit == fruitGroups[1] );
dtTrans = dt << subset( selected rows( 1 ), selected columns( 0 ) );

For( i = 2, i <= N Items( fruitGroups ), i++,
	dt << select where( :Fruit == fruitGroups[i] );
	dtTemp = dt << subset( selected rows( 1 ), selected columns( 0 ) );
	For( k = 1, k <= N Cols( dtTemp ), k++,
		Column( dtTemp, k ) << set name( colNames[k] || " " || Char( i ) )
	);
	// The line below was first published in error it has been corrected
	// dtTrans << Update( With( Data Table( "Subset of Original table 2" ) ) );
	dtTrans << Update( With( dtTemp ) );
	Close( dtTemp, nosave );
);

Re: Need to help to use Split function

raj138 — Thu, 25 Jul 2019 05:36:12 GMT

Hi,

Thank you for the script. While it works amazingly for the table that I provided, I am having difficulty in making it work for my actual data set. Attached is the data set and below is the modified code. Could you please take a look:

Names Default To Here( 1 );
dt = Current Data Table();
colNames = dt << get column names( string );
Summarize( dt, slotGroups = by( :Slot ) );

dt << select where( :Slot == slotGroups[1] );
dtTrans = dt << subset( selected rows( 1 ), selected columns( 0 ) );

For( i = 2, i <= N Items( slotGroups ), i++,
	dt << select where( :Slot == slotGroups[i] );
	dtTemp = dt << subset( selected rows( 1 ), selected columns( 0 ) );
	For( k = 1, k <= N Cols( dtTemp ), k++,
		Column( dtTemp, k ) << set name( colNames[k] || " " || Char( i ) )
	);
	dtTrans << Update( With( Data Table( "Subset of Original table 2" ) ) );
	Close( dtTemp, nosave );
);

Re: Need to help to use Split function

txnelson — Thu, 25 Jul 2019 10:51:52 GMT

Oops....my error, here is a corrected script

Names Default To Here( 1 );
dt = Current Data Table();
colNames = dt << get column names( string );
Summarize( dt, slotGroups = by( :Slot ) );

dt << select where( :Slot == slotGroups[1] );
dtTrans = dt << subset( selected rows( 1 ), selected columns( 0 ) );

For( i = 2, i <= N Items( slotGroups ), i++,
	dt << select where( :Slot == slotGroups[i] );
	dtTemp = dt << subset( selected rows( 1 ), selected columns( 0 ) );
	For( k = 1, k <= N Cols( dtTemp ), k++,
		Column( dtTemp, k ) << set name( colNames[k] || " " || Char( i ) )
	);
	dtTrans << Update( With( dtTemp ) );
	Close( dtTemp, nosave );
);

Re: Need to help to use Split function

raj138 — Thu, 25 Jul 2019 21:30:57 GMT

Works like a charm, thank you very much.