1 Reply Latest reply: Aug 26, 2017 12:38 AM by John Gouws RSS

    Where Not Exists() question

    John Gouws

      I am busy with a Sales project where each day is defined by a Bus_Day_ID. Due to the way the data is uploaded and consolidated each day I am using a 'TakeOn' QVD and a 'Update' QVD. The 'Update' QVD will have Bus_Day_ID's overlapping with the 'TakeOn' QVD.


      What I am finding is that I am only returning the first record in the Where Not Exists function, unless I do a Group By. Is this correct or am I doing something wrong? I have triple checked and my 'overlapping' Bus_Day_ID '1401' has multiple records for the day, but without a group by, it only returns the first record.


      I know I can use the Group By to get the correct answer, but I am wondering if there is a way of not using a Group By?


      In advance I thank you for your input and advice.


      The below Load Scripts are what I am using.

       

      /*Script I would like to use*/

      SALES:

      LOAD

           'Take on' as Source,

           Bus_Day_Id,

           Sales

      FROM SALES_TAKEON.qvd (qvd);

      Concatenate

      LOAD

           'Update' as Source,

           Bus_Day_Id,

           Sales

      FROM SALES_UPDATE.qvd (qvd)

      Where NOT EXISTS(Bus_Day_Id);

       

       

      /*Script with Group By and correct result*/

      SALES:

      LOAD

           'Take on' as Source,

           Bus_Day_Id,

           sum(Sales) as Sales

      FROM SALES_TAKEON.qvd (qvd)

      Group By Bus_Day_Id;

      Concatenate

      LOAD

           'Update' as Source,

           Bus_Day_Id,

           sum(Sales) as Sales

      FROM SALES_UPDATE.qvd (qvd)

      Where NOT EXISTS(Bus_Day_Id)

      Group By Bus_Day_Id;