Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
micsim123
Contributor III
Contributor III

Rank and sequence in table, Qlik sense

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:

       

StudentIDLevelProgrammeEnrollmentIDFromDateSequenceNnumberOfEnrollements
2221MSGam9200001-09-201712
2221MSGam9407701-09-201722
4790MSGam9200801-09-201712
4790MSGam9426701-09-201722
1001MSDdk9202501-09-201711
1001MSGam9429701-09-201711
1789MSGam9200301-09-201711
1167MSDdk9198001-09-201711
1711BSSwu9197601-09-201711
9921BSDim8200001-09-201611
9921BSDim8407701-09-201711

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

Best regards

Michael

1 Solution

Accepted Solutions
mdmukramali
Specialist III
Specialist III

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;

285310.PNG

View solution in original post

2 Replies
mdmukramali
Specialist III
Specialist III

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;

285310.PNG

micsim123
Contributor III
Contributor III
Author

Hi Mohammed

Thanks, it works fine.

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

Regards

Michael