Master Calender creates too many rows

[colinodonnel]

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:

ID	Date	Value £
A	01/01/2017	1000
A	01/02/2017	1500
A	01/01/2018	1600
B	01/05/2015	900
B	01/05/2015	950
B	01/06/2017	500
C	01/02/2018	300
C	01/03/2018	600

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

[colinodonnel]

Hello All,

Would any one know how to get from this:

ID	Min Date	Max Date
A	01/01/2017	01/02/2017
B	01/05/2015	01/06/2017
C	01/02/2018	01/03/2018

to this:

ID	Date
A	01/01/2017
A	Missing Dates
A	01/02/2017
B	01/05/2015
B	Missing Dates
B	01/06/2017
C	01/02/2018
C	Missing Dates
C	01/03/2018

his could solve the issue.

Thank you,

Colin

[colinodonnel]

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]

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]

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;