cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
davek
Level I

How can I create a description column with the names cell values and column names?

I would like to label my graphs with the values and column names for that point (as in a box plot). Currently I do this in Excel by concatenating the column name with the value in the cells. However, if I can do it in JMP, it would save me a significant amount of time. 

 

For example I may have columns

 

A        B         C

1        2         100

1        2         200

2        5         300

 

I would like to create a new column that describes the information in the other columns:

A = 1 : B = 2 : C = 100

A = 1 : B = 2 : C = 200

A = 2 : B = 5 : C = 300

 

So far I have been able to create a list with the column names, but am unable to figure out how to iterate and concatenate the other values.

 

cdt = Current Data Table();
ColNames = {};
ThisLabel = {};
ColNames = cdt << Get Column Names( string );
for(i=3;i<=5;i++)
concat(ThisLabel,ColNames[i]);

// more work is required to get the values from the table

 

Thank you in advance

 

 

2 REPLIES 2
txnelson
Super User

Re: How can I create a description column with the names cell values and column names?

Here is one way to do what you want.  My example shows a hard coded way to solve the problem, and also a more generic method

txnelson_0-1685577481713.png

 

Names Default To Here( 1 );

// Script to create the required column when the names of the columns are known
dt = New Table( "Example 1",
	New Column( "A", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 1, 2] ) ),
	New Column( "B", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 2, 5] ) ),
	New Column( "C", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [100, 200, 300] ) ),
	New Column( "D", Character, Nominal, Formula( "A = " || Char( :A ) || ": B = " || Char( :B ) || ": C = " || Char( :C ) ) )
);

// Example where script examines the data table and from it, generates the new column
dt2 = New Table( "Example 2",
	New Column( "A", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 1, 2] ) ),
	New Column( "B", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 2, 5] ) ),
	New Column( "C", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [100, 200, 300] ) )
);

colNames = dt2 << get column names( string );
// Build the formula in a string variable
theCMD = "dt2 << New Column( \!"D\!", character, Nominal, formula(";
For Each( {col, index}, colNames,
	If( index == 1,
		theCMD = theCMD || "\!" " || col,
		theCMD = theCMD || "\!" : " || col
	);
	theCMD = theCMD || " = \!" || Char( :" || col || ") || ";
);
theCMD = theCMD || "));";
Eval( Parse( theCMD ) );
Jim
BHarris
Level VI

Re: How can I create a description column with the names cell values and column names?

If you don't really need the column names, a faster way is to select your 3 columns, then right click one of the column headers and select "New Formula Column -> Character -> Concatenate with Comma". 

 

Or if you really do need the names, you can right-click the new column header and select "Formula..." and manually edit the formula to include the column header names.  It's slower but doesn't require the JSL step.

 

Note, if all your columns are numeric, you might have to add a temporary column that's a character column to get the "Character" option to show up in the "New Formula Column" list.  We're hoping this gets streamlined in a future version:  https://community.jmp.com/t5/JMP-Wish-List/Enable-string-concatenation-in-quot-New-Formula-Column-qu...