Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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;
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.
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;