Choose Language Hide Translation Bar
Highlighted
MuttonChops
Level III

Transpose columns by variable name JMP14

I am attempting to transpose certain columns that all start with S (and only these columns start with S in my dataset-Sample dt attached).  Sometimes there are more S columns than others so I need to build a list of columns that start with S so I can pass that list to the transpose code.  Of course there are columns with other headers.

 

I see at least 2 other solved posts that are asking the same thing (link1 and link2) but I'm having trouble duplicating the results and I'm wondering if it is due to JMP14 not behaving the same or maybe i'm just doing it wrong?

 

My code is below but here is my error when I run it.  My sample dt is attached.

Substring: Not a String or Numeric in access or evaluation of 'AllCols[i]' , AllCols[/*###*/i]

In the following script, error marked by /*###*/
For( i = 1, i <= N Items( AllCols ), i++,
If(
Substr( AllCols[/*###*/i], 1, 1 ) == "S" &
Is Missing( Num( Substr( AllCols[i], 2 ) ) ) == 0,
Insert Into( ColList, Num( Substr( AllCols[i], 2 ) ) )
)
)

 

		dt= current data table();
		AllCols = dt << get column names;
		ColList = {};
			For( i = 1, i <= N Items( AllCols ), i++,
				If(  Substr( AllCols[i], 1, 1 ) == "S" & Is Missing( Num( Substr( AllCols[i], 2 ) ) ) == 0,
					Insert Into( ColList, Num( Substr( AllCols[i], 2 ) ) )
				);
			);

		// Create Column Lists
		For(i=1,i<=nItems(ColList),i++,
			ColList[i]=column("S" || char(ColList[i]));
		);

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Transpose columns by variable name JMP14

You were doing a lot more work than you needed to.  Here is a rework of your code

dt = Current Data Table();
AllCols = dt << get column names( string );
ColList = {};
For( i = 1, i <= N Items( AllCols ), i++,
	If( Substr( AllCols[i], 1, 1 ) == "S" & Is Missing( Num( Substr( AllCols[i], 2 ) ) ) == 0,
		Insert Into( ColList, AllCols[i])
	)
);

dt2 = dt << Transpose(
	columns( eval( ColList )));
Jim

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

Re: Transpose columns by variable name JMP14

You were doing a lot more work than you needed to.  Here is a rework of your code

dt = Current Data Table();
AllCols = dt << get column names( string );
ColList = {};
For( i = 1, i <= N Items( AllCols ), i++,
	If( Substr( AllCols[i], 1, 1 ) == "S" & Is Missing( Num( Substr( AllCols[i], 2 ) ) ) == 0,
		Insert Into( ColList, AllCols[i])
	)
);

dt2 = dt << Transpose(
	columns( eval( ColList )));
Jim

View solution in original post

Highlighted
MuttonChops
Level III

Re: Transpose columns by variable name JMP14

Jim, so it looks like one key thing i was missing was defining the column names as a string.  Thank you!

Article Labels

    There are no labels assigned to this post.