- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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 );
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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.
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
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