topic Re: How to assign value to a columns in Discussions

How to assign value to a columns

qspringleaf — Wed, 08 Apr 2020 13:45:05 GMT

kindly help on how to generate a script, I want to create a new column, assign value {A, B, C, D, E} by judging column called "rundate", same row with oldest date need assign "A". for rows with same date, need assign same value, for rows with different date need assign different value, like the pic shows.

rundate
4/1/2020 7:18
4/1/2020 7:18
4/1/2020 7:18
4/1/2020 7:47
4/1/2020 8:13
4/1/2020 8:13
4/1/2020 8:13
4/1/2020 8:13
4/1/2020 8:13
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 8:43
4/1/2020 9:13

Re: How to assign value to a columns

txnelson — Wed, 08 Apr 2020 14:13:18 GMT

This formula should do what you want

If( Row() == 1,
	theLetter = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
	counter = 1;
);
If( Lag( :rundate ) != :rundate,
	counter
	++);
Substr( theLetter, counter, 1 );

Re: How to assign value to a columns

qspringleaf — Wed, 08 Apr 2020 14:48:18 GMT

Very appreciate your quick response and so thankful for your suggestion, it works well, one more question, if one more column is added which called group, the data is sort by group & rundate, how to generate "new column" like the picture shows? the difference is 1.) column assigned value is changed, not single character. 2.) considering group name also change, so the assigned value reset to first one "WL15".

new column assigned value is {WL15, WL08, WL06, WL02, WL01}, defined manually.

GROUP rundate
WS12 4/1/2020 7:18
WS12 4/1/2020 7:18
WS12 4/1/2020 7:18
WS12 4/1/2020 7:47
WS12 4/1/2020 8:13
WS12 4/1/2020 8:13
WS12 4/1/2020 8:13
WS12 4/1/2020 8:13
WS12 4/1/2020 8:13
WS12 4/1/2020 8:43
WS12 4/1/2020 8:43
WS12 4/1/2020 8:43
WS12 4/1/2020 8:43
WS12 4/1/2020 8:43
WS12 4/1/2020 8:43
WS12 4/1/2020 8:43
WS12 4/1/2020 9:13
XW32 4/1/2020 9:41
XW32 4/1/2020 10:17
XW32 4/1/2020 10:46
XW32 4/1/2020 10:46
XW32 4/1/2020 10:46
XW32 4/1/2020 11:19
XW32 4/1/2020 14:46
XW32 4/1/2020 14:46
XW32 4/1/2020 14:46
XW32 4/1/2020 14:46
XW32 4/1/2020 14:46
WY07 4/1/2020 15:12
WY07 4/1/2020 15:41
WY07 4/1/2020 18:23
WY07 4/1/2020 18:52

Re: How to assign value to a columns

txnelson — Wed, 08 Apr 2020 15:33:01 GMT

I have a question for you.....have you read the documentation on Formulas? If not, you need to read the Discovering JMP documentation

Help==>JMP Documentation Library Discovering JMP

Here is a modification to the previous formula

If( Row() == 1,
	theGroup = {"WL15", "WL08", "WL06", "WL02", "WL01"};
	counter = 1;
);
If(
	Lag( :Group ) != :Group, counter = 1,
	Lag( :rundate ) != :rundate, counter
	++);
theGroup[counter];

I also suggest, that since the formula is based upon the order of the data, that once the formula is correctly run, that you go into the Column Info for the new column, and remove the formula from the column properties. This will leave the values, but will not rerun the formula if you change the order of the data