Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

andreyfcdk91
Contributor

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
Employee
Employee

Re: Join two tables with different structure

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

manojkulkarni
Valued Contributor II

Re: Join two tables with different structure

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

Hi

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;

shair_abbas
New Contributor III

Re: Join two tables with different structure

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

Arjunarao
Honored Contributor II

Re: Join two tables with different structure

Hi andreyfcdk91

Please follow MayilVahananAdvise.

andreyfcdk91
Contributor

Re: Join two tables with different structure

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...

andreyfcdk91
Contributor

Re: Join two tables with different structure

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.

MVP
MVP

Re: Join two tables with different structure

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

andreyfcdk91
Contributor

Re: Join two tables with different structure

It's not working...

I've made some changes in source tables:

Source1:

   

IdSum1Sum2
12
23
35
43
56
63
72
83
923

Source2:

 

IdSum2
165
234
35
434
53
65
76
93
8

Result table with your code:

  

IdSum1Sum2
1265
2334
355
4334
563
635
726
83
923

But must be:

IdSum1Sum2
1265
2334
355
4334
563
635
726
83
923 3