Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I want to create a field that makes a sequential number.
My initial table:
Person | Case | File | Date |
---|---|---|---|
1 | A | 11 | 01-01-2016 |
1 | A | 12 | 01-01-2016 |
1 | B | 22 | 02-02-2015 |
2 | C | 33 | 01-01-2017 |
3 | D | 44 | 02-02-2018 |
4 | E | 55 | 05-05-2017 |
4 | F | 56 | 04-04-2017 |
4 | A | 11 | 01-01-2016 |
The table below is what I'm trying to achieve.
Person | Case | File | Date | Seq.no | Remarks |
---|---|---|---|---|---|
1 | A | 11 | 01-01-2016 | 1 | The sequence number starts at 1 at the newest date on this person (Person 1) |
1 | A | 12 | 01-01-2016 | 1 | The sequence number stays at 1 as the Case is still the same |
1 | B | 22 | 02-02-2015 | 2 | The sequence number increases as the Case is different |
2 | C | 33 | 01-01-2017 | 1 | New person, new case. Sequence restarts at 1 |
3 | D | 44 | 02-02-2018 | 1 | New person, new case. Sequence restarts at 1 |
4 | E | 55 | 05-05-2017 | 1 | New person, new case. Sequence restarts at 1 |
4 | F | 56 | 04-04-2017 | 2 | Same person as above, but new case. Sequence number increases |
4 | A | 11 | 01-01-2016 | 3 | Same person as above, but new case. Sequence number increases. The case is the same as Person 1 |
May be this
AutoNumber(Case, Person) as SeqNo
PSA.
Hi,
another solution could be:
table1:
LOAD *,
If(Person=Peek(Person),Peek(SeqNo)-(Case<>Peek(Case)),1) as SeqNo
FROM [https://community.qlik.com/thread/299407] (html, codepage is 1252, embedded labels, table is @1);
hope this helps
regards
Marco