topic Re: Join two tables with different structure in QlikView

Join two tables with different structure

Anonymous — Thu, 23 Jul 2015 08:02:15 GMT

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.

Re: Join two tables with different structure

Ralf-Narfeldt — Thu, 23 Jul 2015 08:14:23 GMT

As Id has two possible values for Sum1 and two for Sum2, there is no real data relationship between Sum1=2 and Sum2=65 in the first row. You need to add RowNo() as key, as there is a hidden relationship here in the row number. Also, don't load the empty Sum2 in the first table. This way there is no need to join

Source:

load

Id, Sum1, RowNo() As Key

from Test.xlsx

(ooxml, embedded labels, table is Source1);

Source2:

load*, RowNo() As Key

from Test.xlsx

(ooxml, embedded labels, table is Source2);

Re: Join two tables with different structure

manojkulkarni — Thu, 23 Jul 2015 08:16:27 GMT

Specify the columns to be loaded. Below code is working

Using join, you cann't update column present in first table, So ignore Sum2 column from first loaf.

Source:

load

Id, Sum1,Sum2

from Test.xlsx

(ooxml, embedded labels, table is Source1);

left join(Source)

load

Id,Sum2

from Test.xlsx

(ooxml, embedded labels, table is Source2);

Re: Join two tables with different structure

MayilVahanan — Thu, 23 Jul 2015 08:18:17 GMT

You can try like this also

Directory;

LOAD Id,

Sum(Sum1) As Sum1

FROM

[Test (2).xlsx]

(ooxml, embedded labels, table is Source1)

Group by Id;

Join

LOAD Id,

Sum(Sum2) As Sum2

FROM

[Test (2).xlsx]

(ooxml, embedded labels, table is Source2)

Group by Id;

Re: Join two tables with different structure

shair_abbas — Thu, 23 Jul 2015 08:21:43 GMT

Hey,

I think i have sorted out yours issue in source1 there is field name sum2 which has null values because of this you are getting NULL values in resultant table so you have to remove sum2 from source1 then left join it with source 2.see the attached qvd.

hope this might help.

Regards:

Syed Shair Abbas

Re: Join two tables with different structure

qlikviewwizard — Thu, 23 Jul 2015 08:33:18 GMT

Hi andreyfcdk91

Please follow MayilVahananAdvise.

Re: Join two tables with different structure

Anonymous — Thu, 23 Jul 2015 10:02:34 GMT

Thanks..

But this is simple example.

In my real model i have one table (already loaded in QV) with fields

Id, Sum1, Sum2

I can't reload it with group by function, because it's already joined with other tables...

Re: Join two tables with different structure

Anonymous — Thu, 23 Jul 2015 10:03:34 GMT

Thank you for your help.

But in my real model there may be Id in different rows, and in that case your solution will not be correct.

Re: Join two tables with different structure

swuehl — Thu, 23 Jul 2015 10:09:54 GMT

You somehow need to define which line of Source1 you want to combine with which line of Source2.

It can't be just ID.

I assume it's first apperance of ID in Source1 with first appearance of ID in Source2 etc.

Then try:

Source:

load

Autonumber(Recno(),Id) as Key,

Id,

Sum1

from [Test (1).xlsx]

(ooxml, embedded labels, table is Source1);

left join(Source)

load

Autonumber(Recno(),Id) as Key,

Id,

Sum2

from [Test (1).xlsx]

(ooxml, embedded labels, table is Source2);

Re: Join two tables with different structure

Anonymous — Thu, 23 Jul 2015 10:24:57 GMT

It's not working...

I've made some changes in source tables:

Source1:

Id	Sum1	Sum2
1	2
2	3
3	5
4	3
5	6
6	3
7	2
8	3
9	23

Source2:

Id	Sum2
1	65
2	34
3	5
4	34
5	3
6	5
7	6
9	3
8

Result table with your code:

Id	Sum1	Sum2
1	2	65
2	3	34
3	5	5
4	3	34
5	6	3
6	3	5
7	2	6
8	3
9	23

But must be:

Id	Sum1	Sum2
1	2	65
2	3	34
3	5	5
4	3	34
5	6	3
6	3	5
7	2	6
8	3
9	23	3

Re: Join two tables with different structure

MayilVahanan — Thu, 23 Jul 2015 10:27:47 GMT

Hi,

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

Re: Join two tables with different structure

swuehl — Thu, 23 Jul 2015 10:34:58 GMT

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

Re: Join two tables with different structure

Anonymous — Thu, 23 Jul 2015 11:22:23 GMT

Ok. Thanks..

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

Re: Join two tables with different structure

sorrakis01 — Thu, 23 Jul 2015 11:28:21 GMT

Hi,

Can you upload the original xls?

Thx

Re: Join two tables with different structure

swuehl — Thu, 23 Jul 2015 11:30:57 GMT

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.

Re: Join two tables with different structure

Anonymous — Thu, 23 Jul 2015 11:50:27 GMT

there're no qvd file in attachment...

Re: Join two tables with different structure

Anonymous — Thu, 23 Jul 2015 11:53:55 GMT

Thanks for your help!

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

Re: Join two tables with different structure

Anonymous — Thu, 23 Jul 2015 11:56:04 GMT

Of course... See attachment

Re: Join two tables with different structure

swuehl — Thu, 23 Jul 2015 11:58:14 GMT

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.

Re: Join two tables with different structure

sorrakis01 — Thu, 23 Jul 2015 12:36:03 GMT

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,