Stacking columns by group

[#1]

Hi,

I am new to JSL scripting. I want to stack columns coming from same common name (upto certain length). Such as I have column name "66_CORR_B1_W00", "66_CORR_B1_W01" and so on upto "66_CORR_B1_W12". They should be in single column named""66_CORR_B1". And this will repeat for another column group name starring with "66_CORR_B2". There could be 20 groups of these columns. Finally I want 20 columns that comes from 20 groups of columns.

Here I attach a sample data table.

Thanks in advance.

Muhammad Rahman

[#2 · Accepted solution]

Below is a script that works for the sample data you encluded in your initial Discussion Entry.

Names Default To Here( 1 );
dt = Data Table( "Sample_data" );

ColList = dt << get column names( string, numeric );
// Loop across the column names and find only the 
// columns desired
For( i = N Items( ColList ), i >= 1, i--,
	If( Contains( ColList[i], "66_CORR_B" ) == 0,
		ColList = Remove( ColList, i, 1 )
	)
);
// Sort the columns to ensure they are in the correct order
// for the contiguous stacking
ColList = Sort List( ColList );
// Stack the data
Data Table( "Sample_data" ) << Stack(
	columns( ColList ),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Number of Series( 2 ),
	Contiguous
);

The script is very basic, in that it does not check for situations where there are not equal number of columns for each grouping, the columns to be selected do not follow a very good set of names.

But it should give you an idea of how to procede

[#3]

Thanks you very much.