cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
optflow
Level II

JMP - Column that generates unique row numbers by comparing between existing rows?

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.

1 ACCEPTED SOLUTION

Accepted Solutions
jvillaumie
Level III

Re: JMP - Column that generates unique row numbers by comparing between existing rows?

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

9883_JMP forum.png

View solution in original post

5 REPLIES 5
ms
Super User (Alumni) ms
Super User (Alumni)

Re: JMP - Column that generates unique row numbers by comparing between existing rows?

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.

optflow
Level II

Re: JMP - Column that generates unique row numbers by comparing between existing rows?

Thanks so much!

jvillaumie
Level III

Re: JMP - Column that generates unique row numbers by comparing between existing rows?

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

9883_JMP forum.png

optflow
Level II

Re: JMP - Column that generates unique row numbers by comparing between existing rows?

Thanks so much!

Re: JMP - Column that generates unique row numbers by comparing between existing rows?

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