Adding missing rows for missing dates to a fact without join

[Anonymous]

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

[Marco]

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;

[Anonymous]

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.

[tamilarasu]

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;