Where Not Exists() question

johngouws — Sat, 26 Aug 2017 03:34:07 GMT

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;

Re: Where Not Exists() question

johngouws — Sat, 26 Aug 2017 04:38:57 GMT

I found a solution to me question from Stefan. @

where not exists and only get one line

topic Re: Where Not Exists() question in QlikView

Where Not Exists() question

Re: Where Not Exists() question