Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to derive open cases from a start date and an end date?

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

1 Solution

Accepted Solutions
3 Replies
sundarakumar
Specialist II
Specialist II

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

swuehl
MVP
MVP

Anonymous
Not applicable
Author

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 IDActStartDateActEndDate
100056201/07/201204/07/2012
108945601/08/201409/08/2014
20010004/05/201503/06/2015

and create a second which has all the "missing" dates:

Service User IDActiveDate
100056201/07/2012
100056202/07/2012
100056203/07/2012
100056204/07/2012
108945601/08/2014
108945602/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