Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

colinodonnel
New Contributor III

Master Calender creates too many rows

Hello all,

I am looking to find missing dates pertaining to certain IDs and use the last known value.

The Data Set would look like this:

 

IDDateValue £
A01/01/20171000
A01/02/20171500
A01/01/20181600
B01/05/2015900
B01/05/2015950
B01/06/2017500
C01/02/2018300
C01/03/2018600

The code found here works fine:

ttps://community.qlik.com/blogs/qlikviewdesignblog/2013/02/05/populating-a-sparsely-populated-field#...

It uses the max/min date of the entire data set to create a type of Master Calendar and then joins the table above to this.

The table can be very large so I have created a subsequent bit of code to remove redundant dates (those that fall outside of the Min/Max date for that ID).

This is fine until a) data set and/or b) max/min date range reaches a certain size and then I run into limits with the resulting table size:

s://community.qlik.com/message/803661#803661

QS has a limitation of 2 Billion Distinct values.

To keep the final table size down, is it possible to create a Min/Max variable for each ID?

And then use these variables for a temporary Master Calendar table.

My idea here is to adapt the "Master Calendar" size to the date range of the ID, so that when the data is joined, there are no redundant date rows.

I appreciate that this might create lots and lots of temporary tables along the way.

If not, any other thoughts?

Thanks,

Colin

Tags (1)
4 Replies
colinodonnel
New Contributor III

Re: Master Calender creates too many rows

Hello All,

Would any one know how to get from this:

   

IDMin DateMax Date
A01/01/201701/02/2017
B01/05/201501/06/2017
C01/02/201801/03/2018

to this:

   

IDDate
A01/01/2017
AMissing Dates
A01/02/2017
B01/05/2015
BMissing Dates
B01/06/2017
C01/02/2018
CMissing Dates
C01/03/2018

his could solve the issue.

Thank you,

Colin

colinodonnel
New Contributor III

Re: Master Calender creates too many rows

A CrossTable has been able to solve the first part, namely transform the structure of the table from 3 columns to 2 columns.

MinMaxTable1:
CrossTable (Date1, Date2, 1)
Load * Resident MinMaxTable;
Drop Table MinMaxTable; // this table is the first table containing columns ID, Min Date, Max Date

NoConcatenate
MinMaxTable2:
Load
ID,
Date2 as Date
Resident MinMaxTable1
order by ID Desc, Date2 Desc;
Drop Table MinMaxTable1;

Just need to fill in the missing dates now.

ogautier62
Valued Contributor II

Re: Master Calender creates too many rows

Hi,

from the code above populating .....,

make a group by ID to have a min and max date for each ID

and add ID in temp_table to make join with ID

regards

colinodonnel
New Contributor III

Re: Master Calender creates too many rows

Hi,

Yes I have created the MinMaxTable:

I am struggling with part 2:

and add ID in temp_table to make join with ID

When I do this, the correct Min Max Date does not get added to the ID.

It simply repeats the same Min Max Date.

Perhaps the Variables are not updating in my code?

The code I used is:

TempTable:
Load
ID,
[Date] as Date1
Resident AllData;


MinMaxTable:
Load
[Line ID] as ID,
(Max(DATE([Date],'DD/MM/YYYY')))as [MaxDate],
(Min(DATE([Date],'DD/MM/YYYY'))) as [MinDate]
Resident AllData
Group By [Line ID];
Let vMinDate = Peek('MinDate',-1,'MinMaxTable') - 1;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxTable')    ;

join (TempTable)
Load Date(recno()+$(vMinDate)) as Date
Autogenerate vMaxDate - vMinDate;