topic Re: Help with not Exists in QlikView

Help with not Exists

Wed, 19 Mar 2014 09:27:10 GMT

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

Re: Help with not Exists

jonathandienst — Wed, 19 Mar 2014 09:33:50 GMT

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

Re: Help with not Exists

MK_QSL — Wed, 19 Mar 2014 09:34:15 GMT

Can you clarify what exactly you want?

Can you provide your required result also?

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

Re: Help with not Exists

Wed, 19 Mar 2014 12:43:11 GMT

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

Re: Re: Help with not Exists

maxgro — Wed, 19 Mar 2014 21:00:24 GMT

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;