Rank and sequence in table, Qlik sense

[micsim123]

Hi

I have this data:

Data:

load * inline

["StudentID", "Level", "Programme", "EnrollmentID", "FromDate"

2221,MS,Gam,92000,01-09-2017

2221,MS,Gam,94077,01-09-2017

4790,MS,Gam,92008,01-09-2017

4790,MS,Gam,94267,01-09-2017

1001,MS,Ddk,92025,01-09-2017

1001,MS,Gam,94297,01-09-2017

1789,MS,Gam,92003,01-09-2017

1167,MS,Ddk,91980,01-09-2017

1711,BS,Swu,91976,01-09-2017

9921,BS,Dim,82000,01-09-2016

9921,BS,Dim,84077,01-09-2017 ];

And want a table in Qlik Sense, which shows the same data with two additional columns:

EnrollmentSequence = for each individual StudentID a running sequence (1,2, etc.) for each StudentID with same Programme and Fromdate.

NumberOfEnrollements= The max number of EnrollmentSequence for each StudentID

My desired table should look like this:

StudentID	Level	Programme	EnrollmentID	FromDate	Sequence	NnumberOfEnrollements
2221	MS	Gam	92000	01-09-2017	1	2
2221	MS	Gam	94077	01-09-2017	2	2
4790	MS	Gam	92008	01-09-2017	1	2
4790	MS	Gam	94267	01-09-2017	2	2
1001	MS	Ddk	92025	01-09-2017	1	1
1001	MS	Gam	94297	01-09-2017	1	1
1789	MS	Gam	92003	01-09-2017	1	1
1167	MS	Ddk	91980	01-09-2017	1	1
1711	BS	Swu	91976	01-09-2017	1	1
9921	BS	Dim	82000	01-09-2016	1	1
9921	BS	Dim	84077	01-09-2017	1	1

All ideas are welcome. I would prefer solutions in chart expression. But options in load script are also very welcome.

Best regards

Michael

[mdmukramali · Accepted solution]

Hi Michael,

Script option:

Data:

load *,

StudentID&'-'&Programme&'-'&FromDate as KEY,

AutoNumber(RecNo(),AutonumberHash128(StudentID,Programme,FromDate)) as Sequence

inline

[

"StudentID", "Level", "Programme", "EnrollmentID", "FromDate"

2221,MS,Gam,92000,01-09-2017

2221,MS,Gam,94077,01-09-2017

4790,MS,Gam,92008,01-09-2017

4790,MS,Gam,94267,01-09-2017

1001,MS,Ddk,92025,01-09-2017

1001,MS,Gam,94297,01-09-2017

1789,MS,Gam,92003,01-09-2017

1167,MS,Ddk,91980,01-09-2017

1711,BS,Swu,91976,01-09-2017

9921,BS,Dim,82000,01-09-2016

9921,BS,Dim,84077,01-09-2017

]

;

Left Join(Data)

LOAD

KEY,

Max(Sequence) as Max_Sequence

Resident Data

Group by KEY;

[micsim123]

Hi Mohammed

Thanks, it works fine.

Any ideas on how I could do the same in a chart expression (table)?

Regards

Michael