Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
prashanth1712
New Contributor III

Where not exists is not working?

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

Tags (1)
9 Replies
MVP
MVP

Re: Where not exists is not working?

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

prashanth1712
New Contributor III

Re: Where not exists is not working?

Hi Swuehl,

I am using combination of PO and Policy.

vinieme12
Esteemed Contributor II

Re: Where not exists is not working?

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,

vinieme12
Esteemed Contributor II

Re: Where not exists is not working?

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);

prashanth1712
New Contributor III

Re: Where not exists is not working?

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.

engishfaque
Valued Contributor III

Re: Where not exists is not working?

Dear Prashanth,

Use NOT EXISTS with RIGHT KEEP.

Kind regards,

Ishfaque Ahmed

vinieme12
Esteemed Contributor II

Re: Where not exists is not working?

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;

prashanth1712
New Contributor III

Re: Where not exists is not working?

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.

vinieme12
Esteemed Contributor II

Re: Where not exists is not working?

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

Community Browser