Choose Language Hide Translation Bar
barneyonion
Occasional Contributor

Giving sequential "runs" a value based on minimum to maximum datetime.

Hi Folks,

I have searched for a way to do this but havent found it online yet.

 

I have several runs that have a unique ID in this case I will use the label CAR1 to uniquely identify a vehicle. This vehicle will have multiple timestamps as shown

 

Vehicle   Time

CAR1 10:10am

CAR1 12:30pm

CAR1 17:30pm

 

I want to find a way to be able to label each row sequentially like this using a new column n.

 

Vehicle   Time    N

CAR1 10:10am  1

CAR1 12:30pm  2

CAR1 17:30pm  3

 

Is there a quick way of coding this.

 

I would like JMP to detect the change to the next CAR here so if I then had entries for CAR2 that it has its own counter and starts at 1 for the earlier timestamp.

 

Vehicle   Time    N

CAR1 10:10am  1

CAR1 12:30pm  2

CAR1 17:30pm  3

CAR2 2:10pm  1

CAR2 18:30pm  2

CAR2 19:30pm  3

 

Any help much appreciated.

Thanks!

 

 

 

 

 

 

 

0 Kudos
5 REPLIES 5
txnelson
Super User

Re: Giving sequential "runs" a value based on minimum to maximum datetime.

This is a commonly asked question on the Discussion Forum.  A search of "Sequence" will find many of them. However, here is a simple formula to use

If( :Vehicle != Lag( :Vehicle ),
	count = 1,
	count
	++)
Jim
barneyonion
Occasional Contributor

Re: Giving sequential "runs" a value based on minimum to maximum datetime.

Thanks a lot for your reply @txnelson 

, I went through the LAG and DIF functions and am now familiar with what they do. I tried that formula you suggested, its close to what Im looking for but its labelling both the first instance and second instance within a group as 1 and starts the sequence after that. Is there a way to start the sequence at 1 and increment from there on. This would be extremely useful and its something that I will be using almost daily.

 

Example.jpg

0 Kudos
txnelson
Super User

Re: Giving sequential "runs" a value based on minimum to maximum datetime.

A formula returns the last item calculated, and apparently it is not recognizing the value of count when the first item in the If() function is evaluated.  So the fix is to simply force count to be the last item evaluated in the formula:

If( :CAR != Lag( :CAR ),
	count = 1,
	count
	++);
count;
Jim
barneyonion
Occasional Contributor

Re: Giving sequential "runs" a value based on minimum to maximum datetime.

That works perfectly, thanks!

run counter.jpg

0 Kudos
Highlighted
ian_jmp
Staff

Re: Giving sequential "runs" a value based on minimum to maximum datetime.

You could also try:

Col Rank( :PDATETIME, :CAR )

which might have some advantages (depending on what one can and can't assume about the initial sort order in the table).