Create a Grouped Table

[Anonymous]

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

[rakesh_kumar]

Hi Phil,

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

Regards,
RK

[MarcoWedel]

Hi,

one solution might be:

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

[Anonymous]

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

[Anonymous]

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

[MarcoWedel · Accepted solution]

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