45858_en-US

liliconcom

stack columns with variable suffix

Dear all:

 

I have a huge table with ~30000 parameters(columns) some with the suffix _W00~_W53 and other columns not.

I  want to stack the table with  total columns/54 with a new column W with value 0~53 and keep the other columns without the suffix originally.

 

I know how to used the JMP stack table manually, but it takes very long time and easily to make mistake, especially for the columns without the suffix. 

 

Does any one know how to write the script to stack the colomns based on the columns with the suffix?

 

Thank you,

 

 

 

Re: stack columns with variable suffix

Here is an example of a method that you may find you can use

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA\semiconductor capability.jmp" );

// Get all of the numeric columns
colList = dt << get column names( numeric );

// Find only the columns that match what column name segments I want
// to filter on.  I only want the columns that end in "N0"~"N8"
For( i = N Items( colList ), i >= 1, i--,
 If(
  Substr( Char( colList[i] ), -2, 1 ) != "N" | ((Try( Num( Right( Char( colList[i] ), 1 ) ), -1 ) < 0 |
  Is Missing( Num( Right( Char( colList[i] ), 1 ) ) ) == 1) | (Try( Num( Right( Char( colList[i] ), 1 ) ), 10 ) >= 9 |
  Is Missing( Num( Right( Char( colList[i] ), 1 ) ) ) == 1)),
  colList = Remove( colList, i, 1 )
 );
);

// Stack the data using the filtered list of columns
dtStacked = dt << Stack(
 columns( colList ),
 Source Label Column( "Label" ),
 Stacked Data Column( "Data" )
);

Re: stack columns with variable suffix

The attachment is an example. The actual table is far much larger than that.

To Stack the table based on the suffix _W00~W53

and generate a new column based on the suffix.

However, there are some columns without the suffix _W00~W53 between them.

I still need them after table stack so I can group the data in the future.

So I cannot just use JMP stack function

 

Re: stack columns with variable suffix

The following is an example. The actual table is far much larger than that.

To Stack the table based on the suffix _W00~W53

and generate a new column based on the suffix.

However, there are some columns without the suffix _W00~W53 between them.

I still need them after table stack so I can group the data in the future.

So I cannot just use JMP stack function

 

Die	AA_A_W00	AA_A_W01	AA_A_W02	AA_A_W03	AA_A_W04	AA_A_W05	AA_A_W06	AA_A_W07	AA_A_W08	AA_A_W09	AA_A_W10	AA_A_W11	AA_A_W12	AB_A_W00	AB_A_W01	AB_A_W02	AB_A_W03	AB_A_W04	AB_A_W05	AB_A_W06	AB_A_W07	AB_A_W08	AB_A_W09	AB_A_W10	AB_A_W11	….A1:AA31
1
2
3
4
5
6
7
8
9
Die	W	AA_A	AB_A	AC_A	…..	ZZ_Z	..																				….
1	0
2	0
3	0
4	0
5	0
6	0
7	0
8	0
9	0
1	1
2	1
3	1
4	1
5	1
6	1
7	1
8	1
9	1
….	….

Re: stack columns with variable suffix@hungchih65 :
Can you kindly paste a subset of your table or a sample data set ?