Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Giving sequential "runs" a value based on minimum to maximum datetime.

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Nov 28, 2019 5:45 AM
(228 views)

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!

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

That works perfectly, thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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