I have trouble joining two different tables. Have searched through community for answers with no results. I do believe that i am missing something.
If i am trying to join some data just inline in script it works perfectly:
TEST:
Load * Inline [
ID,NAME1,SURNAME1,REGION,COUNTRY,NUMBER,T1
1,a,aa,EU,UK,323,T1
2,b,bb,EU,UK,454,T1
1,a,aa,EU,UK,323,T1
2,b,bb,EU,UK,454,T1
];
Join(TEST)
LOAD * Inline [
ID,NAME2,SURNAME2,REGION,COUNTRY,NUMBER,T2
1,a,aa,EU,UK,323,T2
2,b,bb,EU,UK,454,T2
1,a,aa,EU,UK,323,T2
2,b,bb,EU,UK,454,T2
];
And i am getting result which i do want to get:
| ID | NAME1 | SURNAME1 | NAME2 | SURNAME2 | REGION | COUNTRY | NUMBER | T1 | T2 |
|---|
| 1 | a | aa | a | aa | EU | UK | 323 | T1 | T2 |
| 2 | b | bb | b | bb | EU | UK | 454 | T1 | T2 |
I do want to create a table with unique combinations from those 2 tables and it works perfectly with Inline.
However, i am loading data from xlsx and the result is not what i have expected
| ID | NAME1 | SURNAME1 | NAME2 | SURNAME2 | REGION | COUNTRY | NUMBER | T1 | T2 |
|---|
| 1 | a | aa | | | EU | UK | 323 | T1 | |
| 1 | | | a | aa | EU | UK | 232 | | T2 |
| 2 | b | bb | | | EU | UK | 454 | T1 | |
| 2 | | | b | bb | EU | UKJ | 454 | | T2 |
Here is a simple script that i have:
TABLE:
LOAD
ID, //can repeat
NAME1,
SURNAME1,
REGION,
COUNTRY,
NUMBER,
'T1' as T1 //field for checking if joined correctly
FROM xlsx file
Join(TABLE)
LOAD
ID, //can repeat
NAME2,
SURNAME2,
REGION,
COUNTRY,
NUMBER,
'T2' as T2 //field for checking if joined correctly
FROM other xlsx file
There is no unique IDs in both tables from xlsx.
There are some lines that matches in both xlsx's.
I ma just trying to find some hidden hint which i have missed.
Help is very appreciated.
