Subscribe Bookmark RSS Feed

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

morenopelaez_p

Community Trekker

Joined:

Feb 24, 2015

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:

IdentifierDateData
M160723-000123-07-163445
M160723-000223-07-162455
M160723-000323-07-162234
M160723-000423-07-162334
M160723-000523-07-162455
M160725-000125-07-167658
M160725-000225-07-166785
M160725-000325-07-167657
M160725-000425-07-165678
M160725-000525-07-166575
M160729-000129-07-167889
M160729-000229-07-165764
M160729-000329-07-164565

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!

1 ACCEPTED SOLUTION

Accepted Solutions
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)

Jim
7 REPLIES
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)

Jim
morenopelaez_p

Community Trekker

Joined:

Feb 24, 2015

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!

ms

Super User

Joined:

Jun 23, 2011

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

morenopelaez_p

Community Trekker

Joined:

Feb 24, 2015

Thanks MS!

Very smart way of including the "0s"!

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

Thanks!

morenopelaez_p

Community Trekker

Joined:

Feb 24, 2015

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.

msharp

Super User

Joined:

Jul 28, 2015

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.

morenopelaez_p

Community Trekker

Joined:

Feb 24, 2015

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.