Qlik Community

Qlik Sense Enterprise Documents

Documents for Qlik Sense related information.

Handling Start and End Dates

richard_pearce6
Valued Contributor

Handling Start and End Dates

i

Recently I've had to develop a system to handle start and end dates within QlikView. The data held a start and end date on each line and the user wanted to be able to select a date range and see all the lines that were active for that period.

This proved a challenge because of the overlap. For example some records started before the period selected but were active during it, some records started during the period and continued past the end and some began way before the period and finished afterwards. The diagram below indicates in green which records I wanted to include in the set.

Start and End Dates.png

First I tried using set analysis and managed to capture a number of the green examples above using various unions although it quickly became too complicated (I'd be interested to know if/how this could be done in Set Analysis simply).

My final solution was to build a link table which contained every date for each record and flagged where the records were active and link the Fact table to the calendar table through this new table.

Start and End Dates Structure.png

I've attached the example qvw and look forward to your comments and improvement ideas

Richard

QlikCentral.Com

Attachments
Comments
simondachstr
Valued Contributor III

Hi Richard,

this is a classic case which can be solved using IntervalMatch() too which in my opinion is much simpler:

RIGHT JOIN (DATA)

IntervalMatch(Date)

LOAD

[Start Date],

[End Date]

Resident DATA;

The down side is that the right join bloats up the fact table (which is not a big issue though because of the same amount of distinct values). You can create a data island/linking table instead but this would then look very similar to your version.

richard_pearce6
Valued Contributor

Hi Martin,

Hope you're well

The script you posted didn't work although I did original feel that interval match could solve the issue and yes expected I'd still have to create a link table to meet my needs (so not simpler really) because in my real life data structure I couldn't be duplicating the fact rows.

Would be interested to see a working example from you......

Richard

richard_pearce6
Valued Contributor

Hi,

Just to add I got the script working now (after the calendar tab) - my mistake....

The complete code for this meathod would be:

LINK_TABLE_TMP:

Load

ID

,[Start Date]

,[End Date]

Resident DATA;

RIGHT JOIN (LINK_TABLE_TMP)

IntervalMatch(Date)

LOAD

[Start Date]

, [End Date]

Resident DATA;

LINK_TABLE:

Load

ID

,Date

Resident LINK_TABLE_TMP;

Drop Table LINK_TABLE_TMP;

simondachstr
Valued Contributor III

Unfortunately I can't upload .qvw files in to the comment section of a document (unlike in a thread, where it's working) but try

https://www.dropbox.com/s/42dyfvfvhkfhx3v/Start%20and%20End%20Date%20MM.qvw?dl=0

MuraliPrasath
Contributor II

This Video is simple and awesome too !!

Selecting Arbitrary Date Ranges - YouTube

Version history
Revision #:
1 of 1
Last update:
‎12-02-2014 08:52 AM
Updated by: