Create sequential number

[nicolai_moller]

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

[sunny_talwar · Accepted solution]

May be this

AutoNumber(Case, Person) as SeqNo

[krishna_2644]

PSA.

[MarcoWedel]

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