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
- :
- Discussions
- :
- JMP - Column that generates unique row numbers by ...

Topic Options

- 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
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Sep 16, 2015 4:08 PM
(2661 views)

Greetings,

If I have the following sample data:

ID 1 Date 1

ID 1 Date 2

ID 1 Date 3

ID 2 Date 4

ID 2 Date 5

ID 2 Date 6

ID 2 Date 7

ID 3 Date 8

ID 3 Date 9

ID 3 Date 10

ID 3 Date 11

ID 3 Date 12

For a given row (aka unique ID and Date pair), how to create a new column to indicate this row is Date number X (of Y rows for each unique ID)?

ID 1 Date 1 1

ID 1 Date 2 2

ID 1 Date 3 3

ID 2 Date 4 1

ID 2 Date 5 2

ID 2 Date 6 3

ID 2 Date 7 4

ID 3 Date 8 1

ID 3 Date 9 2

ID 3 Date 10 3

ID 3 Date 11 4

ID 3 Date 12 5

For this small sample set, I can easily do manually. However, I do not know how to write a script using available JMP/jsl functions to compared between rows to do this for a large data set.

Thank you.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Sep 17, 2015 8:47 AM
(4848 views)

Solution

You could also use one of these formulas, **after using sort by (ID, Date).** One formulas counts the number of times there is a date (any date) for a given ID, the second one counts the number of times there is a **new** date for a given ID (see rows 12 to 14 in screenshot).

If(Row() == 1, 1, If(:ID[Row() - 1,Empty()] == :ID, :Date# for that ID[Row() - 1,Empty()] + 1, If(:ID[Row() - 1,Empty()] != :ID, 1, Empty())))

If(Row() == 1, 1, If(:ID[Row() - 1,Empty()] == :ID, If(:Date[Row() - 1,Empty()] != :Date, :Unique Date# for that ID[Row() - 1,Empty()] + 1, :Unique Date# for that ID[Row() - 1,Empty()]), If(:ID[Row() - 1,Empty()] != :ID, 1, Empty())))

5 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Sep 16, 2015 5:26 PM
(2424 views)

There are several ways to do this. For example, copy the code below and paste it into the formula property of a new column. It's supposed to return the number of all instances of an ID up to the current row (the ID column is assumed to be named "ID").

Eval**(**Eval Expr**(**Col Sum**(**Row**()** <= Expr**(**Row**())**, :ID**)))**

For very big data tables there are probably faster approaches.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Sep 18, 2015 1:54 PM
(2424 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Sep 17, 2015 8:47 AM
(4849 views)

You could also use one of these formulas, **after using sort by (ID, Date).** One formulas counts the number of times there is a date (any date) for a given ID, the second one counts the number of times there is a **new** date for a given ID (see rows 12 to 14 in screenshot).

If(Row() == 1, 1, If(:ID[Row() - 1,Empty()] == :ID, :Date# for that ID[Row() - 1,Empty()] + 1, If(:ID[Row() - 1,Empty()] != :ID, 1, Empty())))

If(Row() == 1, 1, If(:ID[Row() - 1,Empty()] == :ID, If(:Date[Row() - 1,Empty()] != :Date, :Unique Date# for that ID[Row() - 1,Empty()] + 1, :Unique Date# for that ID[Row() - 1,Empty()]), If(:ID[Row() - 1,Empty()] != :ID, 1, Empty())))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Sep 18, 2015 1:53 PM
(2424 views)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Sep 18, 2015 2:16 PM
(2424 views)

For large datasets you might try the following; assuming the first column is sorted as in your example, the expression below will work as a formula in a new column. It takes my machine about 2 seconds on a 1M row dataset.

(Row() - Col Min( Row(), :ID )) + 1

Cheers,

Brady