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

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

davek — Wed, 31 May 2023 21:52:19 GMT

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

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

txnelson — Wed, 31 May 2023 23:58:18 GMT

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

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 ) );

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

BHarris — Thu, 01 Jun 2023 04:13:19 GMT

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-quot-gt/idc-p/582366#M3504