topic Re: Condition on a Join Column in QlikView

Condition on a Join Column

Thu, 15 Jan 2015 17:57:17 GMT

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

Re: Condition on a Join Column

Anonymous — Thu, 15 Jan 2015 18:22:13 GMT

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

];

Re: Condition on a Join Column

saurabh5 — Thu, 15 Jan 2015 18:34:02 GMT

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;

Re: Condition on a Join Column

Thu, 15 Jan 2015 18:44:43 GMT

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!!

Re: Condition on a Join Column

maxgro — Thu, 15 Jan 2015 18:49:42 GMT

see attachment

Re: Condition on a Join Column

Anonymous — Thu, 15 Jan 2015 18:55:59 GMT

I was missing the third piece, which saurabh added.

Re: Condition on a Join Column

Thu, 15 Jan 2015 19:25:52 GMT

Hi Massimo,

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

Re: Condition on a Join Column

Thu, 15 Jan 2015 19:28:14 GMT

Plz check it out