2 Replies Latest reply: Jan 12, 2018 9:17 AM by Michael Simonsen RSS

    Rank and sequence in table, Qlik sense

    Michael Simonsen

      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

        • Re: Rank and sequence in table, Qlik sense
          Mohammed Mukram

          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