Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
philip_hammond
New Contributor

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
MVP
MVP

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

3 Replies
sundarakumar
Valued Contributor II

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

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

MVP
MVP

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

philip_hammond
New Contributor

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

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

Community Browser