JMP Script : to copy the non empty value from multiple column to new column

jinsejoseph

Hi Team,

 

Currently using JMP15 ,
need help in copying the non empty value from mutiple column to a different columns,

not sure i explained issue correctly below is what i am looking for

Here the column name and count is variable

and each row will have only 2 column with value also

Input:

Expected out put

Column1 and column2 are the output column

 

txnelson

Here are a couple of solutions, both of which are modifications of my responses to your previous discussion.

The Loc() function works with missing values, just as it does with zeros.  So the following hard wired JSL works.  The difference is that the Delta is not being produced.  Instead, the first value is being extracted for Column 12 and the second for Column 13

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

dt << New Column( "Column 12",
	Formula(
		x = :data[Row()] || :data1[Row()] || :data2[Row()] || :data3[Row()] || :data4[Row()] ||
		:data5[Row()] || :data6[Row()] || :data7[Row()] || :data8[Row()] || :data9[Row()];
		y = Loc( x );
		Try( x[y[1]], . );
	)
);

dt << New Column( "Column 13",
	Formula(
		x = :data[Row()] || :data1[Row()] || :data2[Row()] || :data3[Row()] || :data4[Row()] ||
		:data5[Row()] || :data6[Row()] || :data7[Row()] || :data8[Row()] || :data9[Row()];
		y = Loc( x );
		Try( x[y[2]], . );
	)
);

To expand this to the situation where the column names are not known, and the number of columns might be different, the below code will work

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

// Get the columns
colNames = dt << get column names( string, continuous );

// Build the first column and formula
theExpr = "dt<<new column(\!"Column 12\!",formula(x = " || colNames[1] || "[Row()]";
For( i = 2, i <= N Items( colNames ), i++,
	theExpr = theExpr || " || " || colNames[i] || "[Row()]"
);
theExpr = theExpr || 
";y = Loc( x );
Try( x[y[1]], . );";

// Execute the Created JSL
Eval( Parse( theExpr ) );

// Build the second column and formula
theExpr = "dt<<new column(\!"Column 13\!",formula(x = " || colNames[1] || "[Row()]";
For( i = 2, i <= N Items( colNames ), i++,
	theExpr = theExpr || " || " || colNames[i] || "[Row()]"
);
theExpr = theExpr || 
";y = Loc( x );
Try( x[y[2]], . );";

// Execute the Created JSL
Eval( Parse( theExpr ) );