topic Re: New Sequence column creation based on another ID columns in Discussions

New Sequence column creation based on another ID columns

venkateshc78 — Sun, 30 Jun 2019 18:42:42 GMT

I've an existing column which has the Customer ID which occurs multiple times in the whole data table on a different date/time. I want to create a sequence such as 1,2,3 for each of the Unique Customer ID based on the date/time variable. How to acheive the same in JMP ?

Sample Data snippet (Customer ID | Date)

ABC | 1-JUN-2019

XYZ | 1-JUN-2019

ABC | 3-JUN-2019

ABC | 4-JUN-2019

XYZ | 7-JUN-2019

I want to create a result like below ((Customer ID | Date | Sequence for Customre ID/Date combination)

ABC | 1-JUN-2019 | 1

XYZ | 1-JUN-2019 | 1

ABC | 3-JUN-2019 | 2

ABC | 4-JUN-2019 | 3

XYZ | 7-JUN-2019 | 2

Please advise

Re: New Sequence column creation based on another ID columns

txnelson — Sun, 30 Jun 2019 20:39:46 GMT

Here is a formula that will work

dt = Current Data Table();
theRow = Row();
theCustomer = :Customer ID;
seq = N Rows( dt << get rows where( :Customer ID == theCustomer & Row() < theRow ) ) + 1;

Re: New Sequence column creation based on another ID columns

ron_horne — Sun, 30 Jun 2019 20:56:50 GMT

Hi @venkateshc78

also try this for more disscussion on the topic:

https://community.jmp.com/t5/Discussions/Syntax-How-do-I-create-an-index-column-by-ID/m-p/9751/highlight/true#M9537

Ron