topic Re: Get 3 Datasets from 2 Tables - Inner join, (Left- Inner Join), (Right- Inner Join) in QlikView

Get 3 Datasets from 2 Tables - Inner join, (Left- Inner Join), (Right- Inner Join)

Anonymous — Thu, 01 Feb 2018 20:31:29 GMT

Hi,

Can someone help me with the below problem -

I have two tables - Table 1 and Table 2. They have one common column and can be joined.

With Qlikview joins, I can get all rows that are common between them(inner join),all the rows in Table 1 (left join) and all the rows in Table2 (Right join).

But, I am looking to get the below 3 datasets -

1. Inner Rows (Rows common to both Table 1 and Table 2)

2. (Left - Inner Join), Rows in Table 1 minus Common Rows in Table 2 (Left minus Inner join Rows)

3. Similarly, (Right - Inner Join), I want rows in Table 2 minus Common Rows with Table 1 (Right - Inner Join Rows)

I am trying to do this in the load statement but any solution will be great.

I did try exclude statement. Doesn't seem to work.

Thank you for your help.

Best,

Harini

Re: Get 3 Datasets from 2 Tables - Inner join, (Left- Inner Join), (Right- Inner Join)

maxgro — Thu, 01 Feb 2018 21:01:31 GMT

try

T1:

load * inline [

f1, val1

a,t1

b,t1t2

c,t1t2

d,t1

e,t1

f,t1

];

T2:

load * inline [

f2, val2

b,t1t2

c,t1t2

x,t2

y,t2

];

TInner:

NoConcatenate LOAD f1 as f, val1 Resident T1;

Inner Join (TInner) load f2 as f, val2 Resident T2;

TLeft_Inner:

NoConcatenate LOAD * Resident T1

Where Not exists (f, f1);

TRight_Inner:

NoConcatenate LOAD * Resident T2

Where Not exists (f, f2);

DROP Table T1;

DROP Table T2;

Re: Get 3 Datasets from 2 Tables - Inner join, (Left- Inner Join), (Right- Inner Join)

Anonymous — Thu, 01 Feb 2018 21:12:55 GMT

Thanks Massimo. That might just work!

Its that in each table I have 15-20 Columns each.

Can I scale this solution to that many columns ?

Thanks.