Left join with multiple where condition issue

[munibridgei2i]

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.

[Kushal_Chawda]

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]

It is working perfect, Thank you Kushal.

[oknotsen]

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 .

[jagan]

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

];