Full outer join

twanqlik — Sat, 14 Oct 2017 09:30:48 GMT

I can use some help with creating a simple 'special' full outer join.

What the goal is:

Load all the registrations from PRODUCT_REGISTRATIONS

Load only the registrations from CONSUMERS_WITH_PC_SOURCE where the %CONSUMER.KEY doesn't exist in PRDUCT_REGISTRATIONS.

Load both datasets in 1 table.

What is the most efficient way to do this?

Currently i'm Outer Joining the tables, with as result that i have consumers from which i've loaded data from the PRODUCT_REGISTRATIONS and CONSUMERS_WITH_PC_SOURCE table. That i want to avoid.

Consumers_Prod_Source:

Load

%CONSUMER.KEY,

PRODUCT_ID,

BU_CTN_KEY,

FLAG_PRODUCT_REGISTERED

Resident PRODUCT_REGISTRATIONS;

Outer Join (Consumers_Prod_Source)

Load

%CONSUMER.KEY,

BU_CTN_KEY,

1 as FLAG_SRC_PC

Resident Consumers_With_PC_Source;

Re: Full outer join

effinty2112 — Sat, 14 Oct 2017 09:53:41 GMT

Hi Twan,

Try this:

Consumers_Prod_Source:

Load

%CONSUMER.KEY,

%CONSUMER.KEY as %CONSUMER.KEY1 //temp field for use in exists function in next load

PRODUCT_ID,

BU_CTN_KEY,

FLAG_PRODUCT_REGISTERED

Resident PRODUCT_REGISTRATIONS;

Concatenate (Consumers_Prod_Source)

Load

%CONSUMER.KEY,

BU_CTN_KEY,

1 as FLAG_SRC_PC

Resident Consumers_With_PC_Source

WHERE NOT EXISTS(%CONSUMER.KEY1,%CONSUMER.KEY);

DROP FIELD %CONSUMER.KEY1;

Regards

Andrew

topic Re: Full outer join in QlikView

Full outer join

Re: Full outer join