Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
twanqlik
Creator
Creator

Full outer join

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;

1 Solution

Accepted Solutions
effinty2112
Master
Master

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

View solution in original post

1 Reply
effinty2112
Master
Master

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