How to create a counter column "within the day"?

[#1]

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!

[#2 · Accepted solution]

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)

[#3]

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!

[#4]

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);

[#5]

Thanks MS!

Very smart way of including the "0s"!

Your solution worked with the example but made JMP crashed with 44000 rows

Thanks!

[#6]

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.

[#7]

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.

[#8]

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.