Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

philmywallet
Not applicable

Create a Grouped Table

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

Tags (2)
1 Solution

Accepted Solutions
MarcoWedel
Not applicable

Re: Create a Grouped Table

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

5 Replies
rakesh_kumar
Not applicable

Re: Create a Grouped Table

Hi Phil,

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

Regards,
RK

MarcoWedel
Not applicable

Re: Create a Grouped Table

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

philmywallet
Not applicable

Re: Create a Grouped Table

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

philmywallet
Not applicable

Re: Create a Grouped Table

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
Not applicable

Re: Create a Grouped Table

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