topic Re: Join field if match in QlikView

Join field if match

Thu, 05 Feb 2015 10:16:51 GMT

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

Re: Join field if match

avinashelite — Thu, 05 Feb 2015 10:19:20 GMT

Hi use applymapping function()

temp:

mapping load

MatchID,

RefEx;

INTERNAL:

LOAD

Amount,

Date,

CCY,

REF

MatchID,

applymapping('temp',MatchID,null()) as Ref;

Re: Join field if match

Miguel_Angel_Baeyens — Thu, 05 Feb 2015 10:21:57 GMT

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

Re: Join field if match

jagan — Thu, 05 Feb 2015 10:24:58 GMT

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.