Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Phil,
Please find the attached document and let me know whether this will be of any help?
Regards,
RK
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
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
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
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