Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
And what script code are you using? Are you checking for a combination of all 4 fields or for specific field values?
Hi Swuehl,
I am using combination of PO and Policy.
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,
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);
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.
Dear Prashanth,
Use NOT EXISTS with RIGHT KEEP.
Kind regards,
Ishfaque Ahmed
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;
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.
Can you post the script and specify what field does the error message point out as missing