Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
munibridgei2i
Contributor
Contributor

Left join with multiple where condition issue

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.

4 Replies
Kush
MVP
MVP

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

];

munibridgei2i
Contributor
Contributor
Author

It is working perfect, Thank you Kushal.

oknotsen
Master III
Master III

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 .

May you live in interesting times!
jagan
Luminary Alumni
Luminary Alumni

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,

F

Inline [

E,F

A1,Accounts

A2,Accounts

A3,Accounts

C1,Count

C2,Count

T1,Talent

T2,Talent

T3,Talent

];