Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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
Ralf-Narfeldt
Employee
Employee

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
Partner - Specialist II
Partner - Specialist II

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

MayilVahanan

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
shair_abbas
Partner - Creator
Partner - Creator

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

qlikviewwizard
Master II
Master II

Hi andreyfcdk91

Please follow MayilVahananAdvise.

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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.

swuehl
MVP
MVP

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

Anonymous
Not applicable
Author

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