Help with not Exists

[#1]

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

[jonathandienst]

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

[MK_QSL]

Can you clarify what exactly you want?

Can you provide your required result also?

May be enclosed file is near what you want...

[#4]

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

[maxgro]

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;