6 Replies Latest reply: Mar 6, 2017 5:25 PM by jade wind RSS

    label records with earliest and latest dates

    jade wind

      Hi everyone,

       

      My data contains people's attendance records. I need to label the records of the first attendance as "First" and latest attendance as "Last". In other words, I need to find the earliest date for the same person and mark it as "First" and latest date for the same person and mark it as "Last". So the table would look like below.

      Person IDDateAttendance
      1111/1/17First
      2222/1/17
      3331/1/17
      1113/1/17
      1114/1/17Last

      How can I do it in Qlik Sense?

       

      Thanks in advance!

        • Re: label records with earliest and latest dates
          Shubham Singh

          MainData:

          LOAD PersonID,Date FROM Table.qvd;

           

          Temp:

          LOAD

          PersonID,

          min(Date) as DateNew,

          'First' as Attendance

          Resident MainData

          Group By PersonID

          ;

           

          concatenate(Temp)

          LOAD

          PersonID,

          max(Date) as DateNew,

          'Last' as Attendance

          Resident MainData

          where not exists(DateNew,Date)

          Group By PersonID

          ;

           

          left join(MainData)

          LOAD

          PersonID,

          DateNew as Date

          ,1 as Flag Resident Temp;

           

          Drop Table Temp;

          Drop Field Flag;

            • Re: label records with earliest and latest dates
              jade wind

              Thanks for your reply Shubham.

               

              I have used your method and the result table is as below:

              Capture.JPG

              I need the PersonIDs that do not have multiple dates to show null as Attendance as I don't want to count them in my calculation of First or Last.

               

              How can I do it?

               

              Thanks in advance!

                • Re: label records with earliest and latest dates
                  Shubham Singh

                  Hi,

                  I have edited my code, that should solve your problem.

                  If a person has single date, it will mark as first.

                    • Re: label records with earliest and latest dates
                      jade wind

                      Thanks Shubham for your efforts. Much appreciated. I have modified your codes as below and it works exactly as I wish now:

                       

                      MainData:

                       

                       

                      LOAD

                          PersonID,

                          "Date"

                      FROM [lib://sample/sample data for testing.xlsx]

                      (ooxml, embedded labels, table is Sheet1);

                       

                       

                      Temp:

                      LOAD

                        PersonID,

                        min(Date) as Date,

                        If(Count(PersonID) >1, 'First') as Attendance

                      Resident MainData

                      Group By PersonID

                      ;

                       

                      concatenate(Temp)

                      LOAD

                        PersonID,

                        max(Date) as Date,

                        If(Count(PersonID) >1, 'Last') as Attendance

                      Resident MainData

                      Group By PersonID

                      ;

                       

                      left join(MainData)

                      LOAD *,1 as Flag Resident Temp;

                       

                      Drop Table Temp;

                      Drop Field Flag;

                       

                      I don't want the single attendance to be marked as either First or Last. And now the above scripts get the result:

                       

                      1.JPG

                • Re: label records with earliest and latest dates
                  jade wind

                  Hi everyone again,

                   

                  I have a new challenge now.

                   

                  I need to count how many times each Person has attended and mark it as Freq for each record (so that whichever record I draw I will know how many times this person has attended). However I find after I have marked the Attendance as First or Last, the attendance in the middle is not marked with anything as Freq (as seen in attached image, the Freq for the middle one is marked as "-"). How can I mark the middle ones with the correct Freq number?

                   

                  Please see attached for source data, and below for loading scripts:

                   

                  YHC:

                  LOAD

                      "Person ID",

                      "Date"

                  FROM [lib://data extracts/testing data.xlsx]

                  (ooxml, embedded labels, table is Sheet1);

                   

                  Temp:

                  LOAD

                    "Person ID",

                    Count("Person ID") as Freq,

                    min("Date") as "Date",

                    If(Count("Person ID") >1, 'First') as Attendance

                  Resident YHC

                  Group By "Person ID"

                  ;

                   

                  concatenate(Temp)

                  LOAD

                    "Person ID",

                    Count("Person ID") as Freq,

                    max("Date") as "Date",

                    If(Count("Person ID") >1, 'Last') as Attendance

                  Resident YHC

                  Group By "Person ID"

                  ;

                   

                  left join(YHC)

                  LOAD *,1 as Flag Resident Temp;

                   

                  Drop Table Temp;

                  Drop Field Flag;