Condition on a Join Column

[#1]

Hi Friends,

I really need help with an issue, I need to join two tables but with a condition on the join column, I don´t know how to use CASE on the join column on QlikView, in few Words i need to join the tables with column A, B, C but if C = 80 you have to discard column C of the join columns:

Example:

Table 1:

LOAD * INLINE [

A, B, C,

1, 1, 3

1, 2, 3

2, 3, 3

2, 1, 3

3, 1, 3

];

JOIN

Table 2:

LOAD * INLINE [

A, B, C, D

1, 1, 3, 1

1, 2, 3, 1

2, 3, 3, 1

2, 1, 3, 1

3, 80, 3, 2

];

Expect Result:

A	B	C	D
1	1	3	1
1	2	3	1
2	3	3	1
2	1	3	1
3	1	3	2

In SQL Server I could do like this:

Select P.Id_SistemaOrigen, P.Id_GrupoPago, P.Id_RamoOrigen, A.Id_Ramo

From TMP_PC P

Left Join Tbl_Rel_AsignaRamo A

On A.Id_SistemaOrigen = P.Id_SistemaOrigen

And A.Id_RamoOrigen = P.Id_RamoOrigen

And Case When A.Id_GrupoPago <> 80 And A.Id_GrupoPago = P.Id_GrupoPago Then 1

When A.Id_GrupoPago = 80 Then 1 Else 0 End = 1

Please I really need help!!!!

Regards

[Anonymous]

Start with this:

Table1:

LOAD * INLINE [

A, B, C,

1, 1, 3

1, 2, 3

2, 3, 3

2, 1, 3

3, 1, 3

];

LEFT JOIN (Table1)

Table2:

LOAD * INLINE [

A, B, C, D

1, 1, 3, 1

1, 2, 3, 1

2, 3, 3, 1

2, 1, 3, 1

3, 80, 3, 2

];

[saurabh5]

Table1:

LOAD * INLINE [

A, B, C,

1, 1, 3

1, 2, 3

2, 3, 3

2, 1, 3

3, 1, 3

];

left JOIN

Table2:

LOAD * INLINE [

A, B, C, D

1, 1, 3, 1

1, 2, 3, 1

2, 3, 3, 1

2, 1, 3, 1

3, 80, 3, 2

];


Table3:
LOAD
A as P,
if(B<>80,B) as Q,
C as R,
D as S Resident Table1;

[#4]

Hi thnkgreen,

Thanks for the reply but your solution does not work for me cause with your solution I get this:

A	B	C	D
1	1	3	1
1	2	3	1
2	1	3	1
2	3	3	1
3	1	3	-

And what i need is:

A	B	C	D
1	1	3	1
1	2	3	1
2	3	3	1
2	1	3	1
3	1	3	2

Thanks!!

[maxgro · Accepted solution]

see attachment

[Anonymous]

I was missing the third piece, which saurabh added.

[#7]

Hi Massimo,

Thanks a lot, you are a life saver!!!

[#8]

Hi

Plz check it out