Conditional join

[#1]

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?

[fkeuroglian]

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

[#3]

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.

[maxgro]

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;