News
On June 1, we’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.
Choose Language Hide Translation Bar
Highlighted
Level III

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: How to create a counter column "within the day"?

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 7
Highlighted
Super User

## Re: How to create a counter column "within the day"?

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
Highlighted
Level III

## Re: How to create a counter column "within the day"?

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!

Highlighted
Super User

## Re: How to create a counter column "within the day"?

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

Highlighted
Level III

## Re: How to create a counter column "within the day"?

Thanks MS!

Very smart way of including the "0s"!

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

Thanks!

Highlighted
Level III

## Re: How to create a counter column "within the day"?

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.

Highlighted
Super User

## Re: How to create a counter column "within the day"?

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.

Highlighted
Level III

## Re: How to create a counter column "within the day"?

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.