topic Re: Create a Grouped Table in QlikView

Create a Grouped Table

Anonymous — Wed, 06 May 2015 14:02:16 GMT

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 — Wed, 06 May 2015 15:11:39 GMT

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

MarcoWedel — Wed, 06 May 2015 22:37:28 GMT

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

Re: Create a Grouped Table

Anonymous — Thu, 07 May 2015 14:37:05 GMT

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

Re: Create a Grouped Table

Anonymous — Fri, 08 May 2015 08:29:58 GMT

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

MarcoWedel — Fri, 08 May 2015 10:19:01 GMT

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