topic Re: Where not exists is not working? in QlikView

Where not exists is not working?

prashanth1712 — Mon, 11 Jul 2016 18:09:23 GMT

Hi all,

I am loading certain data which of yesterday's(I am getting data daily), and after loading today's data, I want to show the only records which are completely new in the output(No repetitions). I have used Where not exists to achieve the same, but it's not giving me the result of my expectation.

For example:

Yesterdays data:

PO Traveler Name Policy date

12 Andy BookingMehtod 07/10/2016

13 Allen BookingMethod 07/10/2016

Today's Data:

PO Traveler Name Policy date

15 Strauss Internal 07/11/2016

13 Allen BookingMethod 07/11/2016

16 Finch BookingMehtod 07/10/2016

Expected result:

PO Traveler Name Policy date

15 Strauss Internal 07/11/2016

16 Finch BookingMehtod 07/10/2016

thanks & regards

Prashanth

Re: Where not exists is not working?

swuehl — Mon, 11 Jul 2016 18:24:39 GMT

And what script code are you using? Are you checking for a combination of all 4 fields or for specific field values?

Re: Where not exists is not working?

prashanth1712 — Mon, 11 Jul 2016 18:38:41 GMT

Hi Swuehl,

I am using combination of PO and Policy.

Re: Where not exists is not working?

vinieme12 — Mon, 11 Jul 2016 18:43:19 GMT

Add an Additional Flag field say, Date_Added.

LOAD

PO,

TravelerName,

Policy,

date,

Date(now(),'DD/MM/YY') as Date_Added

From YourSourceforToday;

This way you will be able to filter for records added on a particular date,

Re: Where not exists is not working?

vinieme12 — Mon, 11 Jul 2016 18:51:05 GMT

Also Have you Pre-loaded Your Data in a separate table before trying to concatenate with NOT EXISTS?

If yes, then try the below, by creating a dummy field for PO.

LOAD

PO,

PO as PO2,

TravelerName,

Policy,

date

From YourPastDataSource;

CONCATENATE

LOAD

PO,

TravelerName,

Policy,

date,

Date(now(),'DD/MM/YY') as Date_Added

From YourSourceforToday

WHERE NOT EXISTS(PO2,PO);

Re: Where not exists is not working?

prashanth1712 — Mon, 11 Jul 2016 18:53:32 GMT

Not like that Vineeth,

I want to show only those records which are not repeated based on the combination of PO and Policy for eg: if a record if a record is available with same PO and same Policy, I dont want to show the that records as I mentioned the example.

Re: Where not exists is not working?

engishfaque — Mon, 11 Jul 2016 18:55:04 GMT

Dear Prashanth,

Use NOT EXISTS with RIGHT KEEP.

Kind regards,

Ishfaque Ahmed

Re: Where not exists is not working?

vinieme12 — Mon, 11 Jul 2016 19:14:05 GMT

Then Create a composite to key to check for Po and Policy as below

Old:

LOAD PO,

Traveler_Name,

PO&'-'&Policy as KeyToCheck,

Policy,

date

FROM OldSource;

RIGHT KEEP

New:

LOAD PO,

Traveler_Name,

Policy,

date

FROM NewSource

Where NOT Exists(KeyToCheck,PO&'-'&Policy)

;

DROP TABLE Old;

Re: Where not exists is not working?

prashanth1712 — Tue, 12 Jul 2016 13:58:12 GMT

Hi Vineeth,

I have done the same, I am getting the error as Field not found when I use the Right keep, If I use join am not getting the error and also result.

Re: Where not exists is not working?

vinieme12 — Tue, 12 Jul 2016 14:34:55 GMT

Can you post the script and specify what field does the error message point out as missing