Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Join field if match

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


3 Replies

Re: Join field if match

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

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

MVP & Luminary
MVP & Luminary

Re: Join field if match

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.