Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have 2 table, Tab A :
Tab A
|
---|
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?
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
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.
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;