Choose Language Hide Translation Bar
Highlighted
venkateshc78
Level I

New Sequence column creation based on another ID columns

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

2 REPLIES 2
Highlighted
txnelson
Super User

Re: New Sequence column creation based on another ID columns

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;
Jim
Highlighted
ron_horne
Super User

Re: New Sequence column creation based on another ID columns

Article Labels

    There are no labels assigned to this post.