Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
As a newcomer to Qlik I am struggling to get my head around how to show numbers of people/services who are active over time.
I have a Fact table with people and the services they receive. Each service has a start date and an end date. Qlik can show the numbers of starters and the numbers of endings each month (from their start/end dates) but cannot show the current active service users over time.
I understand this is because without explicit data to show activity on each day Qlik has nothing to go on. I already have master calendars to fill out white space for the dates mentioned but this does not allow for the time between start and end.
I have experimented with using a second table with interval match to try and draw out those dates between as additional rows.
However I cannot be doing this right as it crashes Qlik each time the script is reloaded.
//TempActive:
//LOAD
//$(varMinDate) + Iterno()-1 As Numact,
//Date($(varMinDate) + IterNo() -1) as TempDateact
//AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxEndDate);
//MasterActive:
//Load
//TempDateact AS ActiveDates,
//Resident TempActive
//Order By TempDateact ASC;
//Drop Table TempActiveCalendar;
//left join (MasterActive)
//IntervalMatch(ActiveDates)
//Load
//ActStartDate as ActiveStartDate,
//ActEndDate as ActiveEndDate
//Resident FACTS;
//left join (MasterActiveCalendar)
//Load
//[Service User ID],
//ActStartDate as ActiveStartDate,
//ActEndDate as ActiveEndDate
//Resident FACTS;
Any views or ideas on how to resolve this preferably in the load script would be most welcome.
Cheers,
Phil
Hi Philip,
I could not completly understand the issue, can you pls share some sample data set and the requirement.
We could not understand what MasterActiveCalendar or TempActiveCalendar are, it would be easiler if you can share this app.
The issue may be bec of this last join where the key is not defined (assuming MasterActiveCalendar and MasterActive are same)
//left join (MasterActiveCalendar)
//Load
//[Service User ID],
//ActStartDate as ActiveStartDate,
//ActEndDate as ActiveEndDate
//Resident FACTS;
-Sundar
Have a look at
Ok I think this has really helped actually.
What I think is working is the following:
MasterActive:
load
[Service User ID],
date(ActStartDate + Iterno()-1) As ActiveDate
resident FACTS
While ActStartDate + IterNo() -1 <= ActEndDate
adapted from the link provided by swuehl above
What I found difficult is the idea that Qlik loops to bring back the records anyway. So I do not need to define a loop which sets out the individual Service User ID's for each group of records.
Hopefully this will now take a tables as follows:
Service User ID | ActStartDate | ActEndDate |
---|---|---|
1000562 | 01/07/2012 | 04/07/2012 |
1089456 | 01/08/2014 | 09/08/2014 |
200100 | 04/05/2015 | 03/06/2015 |
and create a second which has all the "missing" dates:
Service User ID | ActiveDate |
---|---|
1000562 | 01/07/2012 |
1000562 | 02/07/2012 |
1000562 | 03/07/2012 |
1000562 | 04/07/2012 |
1089456 | 01/08/2014 |
1089456 | 02/08/2014 |
It would seem like it creates about the right number of records but the analysis of this is looking a bit funny.
Cheers,
Phil