topic Re: Left join with multiple where condition issue in QlikView

Left join with multiple where condition issue

munibridgei2i — Wed, 25 Nov 2020 16:16:04 GMT

Hi Can anyone help how to fix the below problem.

i have two tables and values are below (Sample data)

Table1:

Load * inline [

A,B,C,D

101,A1,A2,A3

102,C1,C2,C2

103,A1,A2,A3

104,T1,T2,T3

104,C2,C10,C9

];

Table2:

Load * Inline [

E,F

A1,Accounts

A2,Accounts

A3,Accounts

C1,Count

C2,Count

T1,Talent

T2,Talent

T3,Talent

];

when i join the above tables Table1 Left Table2 and the condition is B=E or C= E or D= E so i am getting the result below

A,B,C,D,E

101,A1,A2,A3,Accounts

102,C1,C2,C2,Count

103,A1,A2,A3,Accounts

104,T1,T2,T3,Talent

104,C2,C10,C9,Count

104,C2,C10,C9,-

But i want the below result

A,B,C,D,E

101,A1,A2,A3,Accounts

102,C1,C2,C2,Count

103,A1,A2,A3,Accounts

104,T1,T2,T3,Talent

104,C2,C10,C9,Count

Can you please help me and Thanks in advance.

Re: Left join with multiple where condition issue

Kushal_Chawda — Wed, 23 Aug 2017 10:00:43 GMT

try this

Map:

mapping Load Left(E,1) as E,F Inline [

E,F

A1,Accounts

A2,Accounts

A3,Accounts

C1,Count

C2,Count

T1,Talent

T2,Talent

T3,Talent

];

Data:

Load *, alt(ApplyMap('Map',left(B,1)),ApplyMap('Map',left(C,1)),ApplyMap('Map',left(D,1))) as E

inline [

A,B,C,D

101,A1,A2,A3

102,C1,C2,C2

103,A1,A2,A3

104,T1,T2,T3

104,C2,C10,C9

];

Re: Left join with multiple where condition issue

munibridgei2i — Fri, 08 Sep 2017 05:03:56 GMT

It is working perfect, Thank you Kushal.

Re: Left join with multiple where condition issue

oknotsen — Fri, 08 Sep 2017 07:34:56 GMT

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post; not visible in preview).

If not, please make clear what part of this topic you still need help with .

Re: Left join with multiple where condition issue

jagan — Fri, 08 Sep 2017 08:36:59 GMT

Hi,

You can also try this

Table1:

Load *,

Left(B, 1) AS E

inline [

A,B,C,D

101,A1,A2,A3

102,C1,C2,C2

103,A1,A2,A3

104,T1,T2,T3

104,C2,C10,C9

];

Left Join(Table1)

Load DISTINCT

Left(E, 1) AS E,

Inline [

E,F

A1,Accounts

A2,Accounts

A3,Accounts

C1,Count

C2,Count

T1,Talent

T2,Talent

T3,Talent

];