Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
hrajagopal7
Contributor
Contributor

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

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

1 Solution

Accepted Solutions
maxgro
MVP
MVP

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;

View solution in original post

2 Replies
maxgro
MVP
MVP

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;

hrajagopal7
Contributor
Contributor
Author

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.