Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

johnnymartinez
New Contributor II

Join and fill gaps with a Master Calendar?

I've seen several examples around these forums for QlikView , however the syntax appears to have changed between QlikView and QlikSense on these Master Calendar examples. 

Is anyone using / has some examples of A: Implementing a simple master Calendar , joining one or more tables, and populating null data for intermediate dates where there are gaps in those dates?

For example :

Say I have the below data-set -

QLIK_data_example_01.png

There are missing dates - so if this is joined up to a master Calendar - the data would look like :

QLIK_data_example_02.png

Is it possible in the load script, to work though dates and populate data into the missing dates from the last date where data existed? 

I.E.

QLIK_data_example_03.png

It's doable in Excel on smaller data sets, but in this instance , I'm looking at populating around 3-4 million rows x 6 columns.

1 Solution

Accepted Solutions

Re: Join and fill gaps with a Master Calendar?

Temp:

LOAD

DATE,

LETTER,

NUMBER,

TYPE,

RANK#,

DTNUM

FROM

LoadMeWorkSheet.xlsx

(ooxml, embedded labels, table is DATA);

Temp2:

Load

RowNo() as ID,

DATE as CurrentDate,

Previous(DATE) as NextDate,

LETTER,

NUMBER,

TYPE,

RANK#,

DTNUM

Resident Temp

Order By DATE DESC;

Drop Table Temp;

Final:

Load

*,

Date(CurrentDate + IterNo() - 1) as DATE

Resident Temp2

While CurrentDate + IterNo() - 1 < NextDate;

Drop Table Temp2;

Drop Fields CurrentDate, NextDate, ID;

4 Replies

Re: Join and fill gaps with a Master Calendar?

Temp:

LOAD

DATE,

LETTER,

NUMBER,

TYPE,

RANK#,

DTNUM

FROM

LoadMeWorkSheet.xlsx

(ooxml, embedded labels, table is DATA);

Temp2:

Load

RowNo() as ID,

DATE as CurrentDate,

Previous(DATE) as NextDate,

LETTER,

NUMBER,

TYPE,

RANK#,

DTNUM

Resident Temp

Order By DATE DESC;

Drop Table Temp;

Final:

Load

*,

Date(CurrentDate + IterNo() - 1) as DATE

Resident Temp2

While CurrentDate + IterNo() - 1 < NextDate;

Drop Table Temp2;

Drop Fields CurrentDate, NextDate, ID;

johnnymartinez
New Contributor II

Re: Join and fill gaps with a Master Calendar?

Hi Manish - good example, if they were non-intersecting date ranges this would be perfect, but it seems where date range for one value intersects another, only one selection is entered.

For example - I changed up the data to have several intersecting date ranges for each value like so ( DateCNT column is a count of matching DATE fields - in here, there are 4 entries for 1-Jun-17 and 7-Jun-17 , with 8 Unique Items in the LETTER column )

New_range91.png

However only two lines appear when filtering the selected data on that column :

New_range92.png

Here's the script  :

[Temp]:

LOAD

    "DATE",

    LETTER,

    NUMBER,

    "TYPE",

    RANK#,

    DTNUM,

    DATECNT,

    LETdtRANGE,

    FIRST_DT,

    LAST_DT

FROM [lib://AttachedFiles/LoadMeWorkSheet.xlsx]

(ooxml, embedded labels, table is DATA);

Temp2:@

Load

RowNo()&LETTER as ID,

DATE as CurrentDate,

Previous(DATE) as NextDate,

    LETTER,

    NUMBER,

    "TYPE",

    RANK#,

    DTNUM,

    DATECNT,

    LETdtRANGE,

    FIRST_DT,

    LAST_DT

Resident Temp

Order By DATE DESC;

Drop Table Temp;

Final:

Load

*,

Date(CurrentDate + IterNo() - 1) as DATE

Resident Temp2

While CurrentDate + IterNo() - 1 < NextDate;

Drop Table Temp2;

//Drop Fields CurrentDate, NextDate, ID;

johnnymartinez
New Contributor II

Re: Join and fill gaps with a Master Calendar?

Thanks again for the help!  I could populate the lines manually in excel in this example, but the process I'm working on would be pulling in around 5-6 million rows average - so filling gaps with Excel would not be possible. 

johnnymartinez
New Contributor II

Re: Join and fill gaps with a Master Calendar?

Found a version by Gysbert Wassenaar gwassenaar‌  -  and just modified the fields - it works and populates the intersecting ranges!


Here's the result as a bar chart - as you can see, the fields are now stacking where multiple values exist for the date :

nEW_RANGE_93.png


I altered the ordering to go by LETTER  , DATE  .    Hopefully this helps someone, I must've spent a few hours scouring around, not knowing the correct terminology-  half the battle when it comes to finding the correct answer!


TempData:

LOAD

    [DATE] as Date,

    LETTER,

    NUMBER,

    [TYPE],

    RANK#,

    DTNUM,

    DATECNT,

    LETdtRANGE,

    FIRST_DT,

    LAST_DT

FROM [lib://AttachedFiles/LoadMeWorkSheet.xlsx]

(ooxml, embedded labels, table is DATA);

Temp2:

load *, if(previous(LETTER)=LETTER, previous(Date),Date+1) as NextDate

Resident TempData

order by LETTER, Date desc;

drop table TempData;

Result:

load LETTER, date(Date + IterNo() -1) as Date,

    NUMBER,

    [TYPE],

    RANK#,

    DTNUM,

    DATECNT,

    LETdtRANGE,

    FIRST_DT,

    LAST_DT

Resident Temp2

while Date + IterNo() - 1 < NextDate

order by LETTER, Date;

drop table Temp2;