Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
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);
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;
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
Hi andreyfcdk91
Please follow MayilVahananAdvise.
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...
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.
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);
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 |