Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone
I've got the attached list of dates, accounts, items, qty and costs. Not all items are present for all accounts on all dates, so I cannot (for instance) look on which dates there were zero sales for a particular item on a particular account (the data doesn't exist).
How can I generate rows with zero QTY and zero Cost where it does not exist? I know it's done with not Exists but I just dont seem to get it right. What happens if more dimensions are added (say region etc) - how should the script be adjusted.
Thanks for the help.
Pieter
Hi
It may be sufficient to add a master calendar to your model which includes all the dates from a minimum to a maximum date. The calendar should be kept in a separate table and associated with the transaction date. I prefer this to adding fake zero value transactions as these affect counts and averages.
There are many links here if you search for "master calendar".
HTH
Jonathan
Can you clarify what exactly you want?
Can you provide your required result also?
May be enclosed file is near what you want...
Hi
Apologies if this wasnt clear.
Input Data:
Date Account Item QTY Cost
D1 A1 I1 10 10
D1 A2 I2 50 100
Output Data:
D1 A1 I1 10 10
D1 A1 I2 0 0
D1 A2 I1 0 0
D1 A2 I2 50 100
Zero's inserted for D1,A1,I2 and D1,A2,I1 because they were not present in the input data.
Regards
I think a way is to cross join the field Date, Account, Item (cartesian product)
Result
Script
Input:
load * inline [
Date , Account , Item , QTY , Cost
D1 , A1 , I1 , 10, 10
D1 , A2 , I2 , 50 , 100
];
Tmp:
load Distinct Date Resident Input;
join (Tmp) LOAD Distinct Account Resident Input;
join (Tmp) LOAD Distinct Item Resident Input;
Left join (Tmp) load * Resident Input;
Table:
NoConcatenate load
Date , Account , Item ,
alt(QTY,0) as QTY , alt(Cost,0) as Cost
Resident Tmp;
DROP Table Input, Tmp;