topic Conditional join in QlikView

Conditional join

Thu, 19 Jun 2014 17:00:30 GMT

I have 2 table, Tab A :

Tab A

KEY 1	KEY 2	INVOICE 1
1		1111
9	4	222
6		333

Tab B

KEY 1	KEY 2	INVOICE 2
1		444
10	4	555
3		666

I want to join the two table with a conditiona join : If KEY 1 TAB A = KEY 1 TAB B, join by KEY 1

If KEY 1 TAB A not = KEY 1 TAB B, then,

If exists KEY 2, join by KEY 2

In the final table i want to see INVOICE 1 AND INVOICE 2.

How to set this in script?

Re: Conditional join

fkeuroglian — Thu, 19 Jun 2014 17:04:26 GMT

TABA:

Load KEY1,

KEY2,

KEY1&'-'&KEY2 AS KEY

INVOICE1

FROM TABA;

LEFT JOIN(TABA)

Load KEY1&'-'&KEY2 AS KEY

INVOICE2

FROM TABB;

--------------------------------------------------------------------------------------------------------------------------

This will show TABA, with KEY1,KEY2,INVOICE1,INVOICE2

try that

good luck

Fernando

Re: Conditional join

Thu, 19 Jun 2014 17:14:18 GMT

Hi,

I guess it works:

TabA:

Load

KEY1,

KEY2,

INVOICE1

from TABA;

LeftJoin(TabA)

Load

KEY1,

INVOICE2

from TABB;

LeftJoin(TabA)

Load

KEY2,

INVOICE2

from TABB;

Best regards.

Re: Conditional join

maxgro — Thu, 19 Jun 2014 17:18:26 GMT

below result and script


TabA: 
load KEY1 as KEY1A, KEY2 as KEY2A, INVOICE1 inline [
KEY1, KEY2, INVOICE1
1,, 1111
9, 4, 222
6, , 333
];

join (TabA) load KEY1 as KEY1B, KEY2 as KEY2B, INVOICE2;
load * inline [
KEY1, KEY2, INVOICE2
1, , 444
10, 4, 555
3, ,666
];

Final:
NoConcatenate 
load
  KEY1A, KEY2A, INVOICE1, INVOICE2
Resident TabA
where
  KEY1A=KEY1B
  or ( 
  (not KEY1A=KEY1B) and (KEY2A=KEY2B and len(trim(KEY2A))>0)
  )
  ; 

DROP Table TabA;