topic Re: Adding missing rows for missing dates to a fact without join in QlikView

Adding missing rows for missing dates to a fact without join

Anonymous — Wed, 21 Nov 2018 09:47:44 GMT

Hi,

I have a source table with "account", "date", "value"

I want to load that table and adding for each account the missing dates with empty value from his minimum date to the maximum date in the source table

This needs to be done in one load (preceding load are ok)

Anyone have an idea how to do this?

Thanks

Re: Adding missing rows for missing dates to a fact without join

Marco — Wed, 21 Nov 2018 10:04:10 GMT

Hi,

You can try with this:

Table:
NoConcatenate
LOAD
Account,
Date,
Value
From
Source
;

Calendar:
NoConcatenate
LOAD
Account,
Min(Date) as DateMin,
Max(Date) as DateMax
Resident
Table
Group By
Account;

Join (Table)
LOAD
Account,
Date(iterno()+MinDate) as Date
Resident
Calendar
While iterno()+MinDate <= MaxDate
;
Drop Table Date;

Re: Adding missing rows for missing dates to a fact without join

Anonymous — Wed, 21 Nov 2018 10:09:51 GMT

this was my first try

i`m working on a very large data set, can`t use join.

needs to be done in one load of the source table, can be done with preceding load.

Re: Adding missing rows for missing dates to a fact without join

tamilarasu — Wed, 21 Nov 2018 10:58:39 GMT

I hope this will not take much time since we are taking resident of only two fields from main table and concatenating with main table.

Data:
Load
Account & Date as TempKey,
Account,
Date,
Value
FROM Source;
Concatenate (Data)
Data:
Load * where not Exists(TempKey);
LOAD Account & date(Min -1 + IterNo(),'DD/MM/YYYY') as TempKey,
Account,
date(Min -1 + IterNo(),'DD/MM/YYYY') as Date,
'' as Value
While IterNo() <= Max - Min +1;
LOAD Account, Max(Date) as Max,
Min(Date) as Min
Resident Data Group By Account;
DROP Field TempKey;