5 Replies Latest reply: May 8, 2015 6:19 AM by Marco Wedel RSS

    Create a Grouped Table

    Phil Speight

      Hi

       

      I am wanting to create a table from a set of data to show when the last date ta staff member was off sick for more than 5 days consecutively.

       

      My data has each date the person was sick.  I have attached a spreadsheet which shows sample data and expected table output.

       

      Please note the data also contains other absences (not just sickness) so I need a where statement in there too.

       

       

      Can anyone help, it is driving me insane !!!

       

       

      Regards

       

       

       

      Phil

        • Re: Create a Grouped Table
          Rakesh Kumar

          Hi Phil,

           

          Please find the attached document and let me know whether this will be of any help?

           

          Regards,
          RK

          • Re: Create a Grouped Table
            Marco Wedel

            Hi,

             

            one solution might be:

             

            QlikCommunity_Thread_162970_Pic3.JPG

             

            QlikCommunity_Thread_162970_Pic4.JPG

             

            QlikCommunity_Thread_162970_Pic5.JPG

             

            QlikCommunity_Thread_162970_Pic1.JPG

             

            QlikCommunity_Thread_162970_Pic2.JPG

             

            tabStaffPresence:
            LOAD * FROM [https://community.qlik.com/servlet/JiveServlet/download/769058-162831/Last_Sick.xlsx] (ooxml, embedded labels, table is Data);
            
            tabTemp:
            LOAD Person,
                Date,
                If(Person<>Previous(Person) or Absence<>Previous(Absence), Date, Peek(StartDate)) as StartDate,
                Absence
            Resident tabStaffPresence
            Order By Person, Date;
            
            tabStaffAbsences:
            LOAD *,
                EndDate-StartDate+1 as DurationDays;
            LOAD Person,
                StartDate,
                Date as EndDate,
                Absence as AbsenceType
            Resident tabTemp
            Where (Person<>Previous(Person) or Absence<>Previous(Absence)) and Len(Absence)
            Order By Person, Date desc;
            
            DROP Table tabTemp;
            

             

            hope this helps

             

            regards

             

            Marco

              • Re: Create a Grouped Table
                Phil Speight

                Marco

                 

                This looks like what I need, however I am linking to another table, which means this sickness table can only have a single row of data for each person, as per the output table in the spreadsheet.

                 

                Is it possible to make it a table in the load script (of latest Long Term Sickness) rather than doing it as analysis?

                 

                 

                Regards

                 

                 

                 

                Phil

                  • Re: Create a Grouped Table
                    Marco Wedel

                    Hi,

                     

                    starting from my previous post, this preceding load added to the tabStaffAbsences LOAD should work:

                     

                    LOAD Person,
                        FirstSortedValue(AbsenceType, -EndDate) as AbsenceType,
                        Date(FirstSortedValue(StartDate, -EndDate)) as StartDate,
                        Date(Max(EndDate)) as EndDate,
                        FirstSortedValue(DurationDays, -EndDate) as DurationDays
                    Where DurationDays>=5
                    Group By Person;
                    

                     

                    but I guess, there are shorter solutions to your question ...

                     

                    hope this helps

                     

                    regards

                     

                    Marco

                • Re: Create a Grouped Table
                  Phil Speight

                  Marco

                   

                  This looks like what I need, however I am linking to another table, which means this sickness table can only have a single row of data for each person, as per the output table in the spreadsheet.

                   

                  Is it possible to make it a table (of latest Long Term Sickness rather than doing analysis?

                   

                   

                  Regards

                   

                   

                   

                  Phil