215541_en-US

liliconcom

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

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;

Re: New Sequence column creation based on another ID columns

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