Where not exists is not working?

[prashanth1712]

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

[swuehl]

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

[prashanth1712]

Hi Swuehl,

I am using combination of PO and Policy.

[vinieme12]

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]

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]

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]

Dear Prashanth,

Use NOT EXISTS with RIGHT KEEP.

Kind regards,

Ishfaque Ahmed

[vinieme12]

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]

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]

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