Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Help with not Exists

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

4 Replies
MVP
MVP

Re: Help with not Exists

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Re: Help with not Exists

Can you clarify what exactly you want?

Can you provide your required result also?

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

Not applicable

Re: Help with not Exists

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

MVP
MVP

Re: Re: Help with not Exists

I think a way is to cross join the field Date, Account, Item (cartesian product)


Result

2014-03-19 21_38_51-QlikView x64 - [C__Users_mgrossi_Downloads_111209.qvw_].png

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;

Community Browser