Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Hi Mohammed
Thanks, it works fine.
Any ideas on how I could do the same in a chart expression (table)?
Regards
Michael