Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm working with two data sets. One for internal bookings and one for external.
I'm only interested in the internal bookings, however I need to add a reference field from the external data set for each booking.
So, first I want to load the internal bookings, something like below:
INTERNAL:
LOAD
Amount,
Date,
CCY,
REF
MatchID,
Then I need to add the REF from the external data set, where the MatchID is identical.
EXTERNAL:
LOAD
AmountEx,
DateEx,
CCYEx,
RefEx,
MatchID,
Then, for my final table I want the internal dataset, plus the RefEx, whenever the MatchID is identical.
FINAL TABLE:
Amount,
Date,
CCY,
REF
MatchID,
RefEx
Is there an easy way of doing this?
Thanks in advance,
Olle
Hi use applymapping function()
temp:
mapping load
MatchID,
RefEx;
INTERNAL:
LOAD
Amount,
Date,
CCY,
REF
MatchID,
applymapping('temp',MatchID,null()) as Ref;
Use the WHERE EXISTS() clause and function:
MappingLoad:
MAPPING LOAD MatchID,
RefEx
FROM EXTERNAL;
Internal:
LOAD Amount,
Date,
CCY,
REF
MatchID,
ApplyMap('MappingLoad', MatchID) AS RefEx
FROM Source
WHERE EXISTS (MatchID);
Miguel
HI,
Try like this
Internal:
LOAD
Amount,
Date,
CCY,
REF
MatchID
FROM Internal;
LEFT JOIN
LOAD
DISTINCT MatchID,
RefEx
FROM EXTERNAL;
If you want only the matching records then you can use INNER JOIN.
Regards,
Jagan.