cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
qspringleaf
Level III

How to assign value to a columns

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

Untitled.bmp

3 REPLIES 3
txnelson
Super User

Re: How to assign value to a columns

This formula should do what you want

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

Re: How to assign value to a columns

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.

 

Capture.JPG

 

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

txnelson
Super User

Re: How to assign value to a columns

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

Jim