Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Join two tables with different structure

Good day!

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

Source1:

   

IdSum1Sum2
12
13
25
33
46
43
52
63

Source2:

 

IdSum2
165
134
25
334
43
45
56
63

In result i need to get such table:

   

IdSum1Sum2
1265
1334
255
3334
463
435
526
63

3

Model and source file in attachment.

Please, help.

21 Replies
Anonymous
Not applicable
Author

in attachment model and source file

necessary result table:

   

IdSum1Sum2
1234
3356
65
1933
45665
swuehl
MVP
MVP

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);