Join and fill gaps with a Master Calendar?

[johnnymartinez]

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 -

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

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.

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.

[MK_QSL · Accepted solution]

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]

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 )

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

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]

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]

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 :

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;