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.

[MayilVahanan]

Hi,

In that case, you can use Order by Id on both table.

[swuehl]

Yes, I see:

Source:

load

Autonumber(Recno(),'S1'&Id) as Key,

Id,

Sum1

from [Test (1).xlsx]

(ooxml, embedded labels, table is Source1);

left join(Source)

load

Autonumber(Recno(),'S2'&Id) as Key,

Id,

Sum2

from [Test (1).xlsx]

(ooxml, embedded labels, table is Source2);

[Anonymous]

Ok. Thanks..

Tell me please, why did you use such values 'S1' 'S2' in Keys?

[sorrakis01]

Hi,

Can you upload the original xls?

Thx

[swuehl]

The autonumber() function will use the same counter for an ID within the same QVW, but I want to use a counter per ID occurence per Source table. That's why I added an arbitrary identifiers S1 and S2 to the Autonumber bucket ID for the two source tables.

[Anonymous]

there're no qvd file in attachment...

[Anonymous]

Thanks for your help!

But in first table there no field Sum2. In my real model in this field may be some values...

[Anonymous]

Of course... See attachment

[swuehl]

And how would you like to handle this?

You can rename one of the two Sum2 fields, then join the tables. this should get you all lines, and all Sum2 values.

If you want to add the two Sum2 fields (remember, one has been renamed), you can do this in a subsequent resident load.

Similar if you don't want to add the values, but keep the values from one sheet and drop the other.

[sorrakis01]

Hi andrey,

And you can't create a qvd with this structure (Example of Mayil is Good) and after load it with the others qvds?

Regards,