4 Replies Latest reply: Mar 19, 2014 5:00 PM by Massimo Grossi RSS

    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

        • Re: Help with not Exists
          Jonathan Dienst

          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

          • Re: Help with not Exists
            Manish Kachhia

            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

                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

                  • Re: Re: Help with not Exists
                    Massimo Grossi

                    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;