Hello,
I have a table with measurements, some are done within the day and some across different days. I would like to create a column that gives the measurement a unique identifier string using the date and a SEQUENTIAL NUMBER FOR THE DAY.
This would look like this:
Identifier | Date | Data |
M160723-0001 | 23-07-16 | 3445 |
M160723-0002 | 23-07-16 | 2455 |
M160723-0003 | 23-07-16 | 2234 |
M160723-0004 | 23-07-16 | 2334 |
M160723-0005 | 23-07-16 | 2455 |
M160725-0001 | 25-07-16 | 7658 |
M160725-0002 | 25-07-16 | 6785 |
M160725-0003 | 25-07-16 | 7657 |
M160725-0004 | 25-07-16 | 5678 |
M160725-0005 | 25-07-16 | 6575 |
M160729-0001 | 29-07-16 | 7889 |
M160729-0002 | 29-07-16 | 5764 |
M160729-0003 | 29-07-16 | 4565 |
The column I need JMP to create is the "Identifier column". I have been playing with the Row(), Subscript() and other functions but I have only managed to make JMP crash.
I have this piece of formula for the first part but I am struggling with the counter part:
"M" || Right( Char( Year( :Date ) ), 2 ) ||
Char( Month( :Date ) ) || Char( Day( :Date ) )
|| "-" || ????
Any suggestions are really welcome!
Here is one way to do it:
if(row()==1,countval=1);
if(:date==lag(:date),countval=sum(countval,1),countval=1);
"M" || Right( Char( Year( :Date ) ), 2 ) ||
Char( Month( :Date ) ) || Char( Day( :Date ) )
|| "-" || char(countval)
Here is one way to do it:
if(row()==1,countval=1);
if(:date==lag(:date),countval=sum(countval,1),countval=1);
"M" || Right( Char( Year( :Date ) ), 2 ) ||
Char( Month( :Date ) ) || Char( Day( :Date ) )
|| "-" || char(countval)
Thanks Jim!
I see no reason why this solution would not work...but it does not seem to work with my table...
I am attaching it in case someone can figure out why...
The other two small issues I have is that I need the string to have a constant size and therefore I would need the day and month numbers to have the "0" before the figure. Also for the sequential number. "0001" Is there any neat way to correct this?
Thanks again!
Another way:
Paste the below formula in to the column formula editor. The table needs not to be sorted after date.
"M" || Right(Char(Year(:Date)), 2) ||
Right("0" || Char(Month(:Date)), 2) ||
Right("0" || Char(Day(:Date)), 2) || "-" ||
Right("000" || Char(Eval(Eval Expr(Col Number(Row(), :Date, Row() <= Expr(Row()))))), 4);
Thanks MS!
Very smart way of including the "0s"!
Your solution worked with the example but made JMP crashed with 44000 rows
Thanks!
So, I have figured out that what I had was some kind of timestamp rather than a date, this is why it was not working in the example file I posted...sorry for the mistake defining the need! However, I have solved that with "dayofyear".
I have used one piece of each solution proposed, thanks to both txnelson and MS for the help!
This is the final code that works for me:
If(Row() == 1, countval = 1); If(Day Of Year(:Date) == Lag(Day Of Year(:Date)), countval = Sum(countval, 1), countval = 1); "M" || Right(Char(Year(:Date)), 2) || Right("0" || Char(Month(:Date)), 2) || Right("0" || Char(Day(:Date)), 2) || "-" || Right("000" || Char(countval), 4)
Also, attached the file with the final column.
This is neither here nor there, but marking your own answer correct when TXNelson and MS clearly solved the issue in the OP is very unsavory.
msharp, my apologies, I really did not mean to undervalue the help from both txnelson and MS. Credit to the solution goes to them fully. I tried to be appreciative of their help in my final post.
I marked my response as the correct one to enable easy location of the complete solution for future users searching through the forum. However, if this is perceived as me taking credit for the solution, I am rectifying it straight away. Again, my apologies if anyone was got offended.