Join two tables with different structure

[Anonymous]

Good day!

In my model i try join two tables from excel source:

Source1:

Id	Sum1	Sum2
1	2
1	3
2	5
3	3
4	6
4	3
5	2
6	3

Source2:

Id	Sum2
1	65
1	34
2	5
3	34
4	3
4	5
5	6
6	3

In result i need to get such table:

Id	Sum1	Sum2
1	2	65
1	3	34
2	5	5
3	3	34
4	6	3
4	3	5
5	2	6
6	3	3

Model and source file in attachment.

Please, help.

[Anonymous]

in attachment model and source file

necessary result table:

Id	Sum1	Sum2
1	2	34
3	3	56
6	5
19	3	3
45	6	65

[swuehl]

One possible solution:

Source:
load
Autonumber(Recno(),'S1'&Id) as Key,
Id,
Sum1,
Sum2
from [Test.xlsx]
(ooxml, embedded labels, table is Source1);


left join(Source)
load
Autonumber(Recno(),'S2'&Id) as Key,
Id,
Sum2 as AddSum2
from [Test.xlsx]
(ooxml, embedded labels, table is Source2);

RESULT:
LOAD Id, Sum1, if(len(trim(Sum2)),Sum2, AddSum2) as Sum2
RESIDENT Source;

DROP TABLE Source;

Another one:

MAP:
MAPPING
LOAD
Id & '_' & Autonumber(Recno(),'S2'&Id) as F1 ,Sum2 as F2
from [Test.xlsx]
(ooxml, embedded labels, table is Source2); 

RESULT:
LOAD
//Autonumber(Recno(),'S1'&Id) as Key,
Id,
Sum1,
if(len(trim(Sum2)), Sum2, applymap('MAP', Id & '_' & Autonumber(Recno(),'S1'&Id),NULL() )) as  Sum2
from [Test.xlsx]
(ooxml, embedded labels, table is Source1);