Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm struggling with the following problem.
I currently have the following data:
Person | Date |
---|---|
Person1 | 3/1/2015 |
Person1 | 6/1/2015 |
Person1 | 10/2/2015 |
Person2 | 3/15/2015 |
Person2 | 4/15/2015 |
Person2 | 09/20/2015 |
Person3 | 1/2/2015 |
I would like to iterate through the data and create a third column that numbers the two columns above in chronological order for each unique "person" value. The data would look like table below.
Person | Date | Number |
---|---|---|
Person1 | 3/1/2015 | 1 |
Person1 | 6/1/2015 | 2 |
Person1 | 10/2/2015 | 3 |
Person2 | 3/15/2015 | 1 |
Person2 | 4/15/2015 | 2 |
Person2 | 09/20/2015 | 3 |
Person3 | 1/2/2015 | 1 |
Thanks in advance.
LOAD AutoNumber(Person&Date,Person) as Number,
Person,
Date
FROM
[https://community.qlik.com/thread/185296]
(html, codepage is 1252, embedded labels, table is @1);
temp:
Load Person, Dates from file;
Fact:
Load Person, Date, if(Peek(Person) = Person, Peek(Number)+1, 1) as Number
Resident temp order by Dates asc;
Drop table temp;
LOAD AutoNumber(Person&Date,Person) as Number,
Person,
Date
FROM
[https://community.qlik.com/thread/185296]
(html, codepage is 1252, embedded labels, table is @1);
This worked perfectly. Thank you.
Craig Linderman
Kindly close the thread by selection correct answer !