Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Loading "Complementary" records from 2 files:
Dear all, ought to be simple but I haven't come up come up with a solution:
I have a file that fully describes Entity A, and another file that as some valid information about Entity A that need to use, although the columns (properties' ) names are different in both files:
File EntityA_WellDescribed.xls
Name | Code | Address |
a | 1 | A1 |
b | 2 | B2 |
c | 3 | B3 |
File EntityA_NOT_WellDescribed.xls
Name_NWF | Code_NWF | NotImportant |
a | 1 | BLABLA |
b | 2 | BLOBLO |
d | 4 | BLAGBLAG |
e | 5 | GLODLOG |
I need to create a table that has all the information from file EntityA_WellDescribed.xls plus the usable part of the information from File EntityA_NOT_WellDescribed.xls, for all the Entities that do not exist in EntityA_WellDescribed.xls; the resulting table should be:
Name | Code | Address |
a | 1 | A1 |
b | 2 | B2 |
c | 3 | B3 |
d | 4 | NA |
e | 5 | NA |
The solution seems to be simple:
FullTable:
LOAD Name, Code, Adreess FROM EntityA_WellDescribed.xls;
LOAD Name_NWF as Name, Code_NWF as Code, 'NA' as Adreess
FROM EntityA_NOT_ WellDescribed.xls where not exists (Code) ;
But of course it doesn't work . Can you please people please help ?
Thanks in advance,
ptspy
To get the correct/desired result please Use below script
A:
LOAD
Name
,
Code
,
Address
FROM
EntityA_Welldefined.xlsx
(
ooxml, embedded labels, table is Sheet1);
B:
LOAD
Name_NWF
as Name,
Code_NWF
as Code,
'NA'
asAddress
FROM
EntityA_notWelldefined.xlsx
(
ooxml, embedded labels, table is Sheet1)
Where
not exists (Code,Code_NWF)
;
Will generate thecorrect result
'
Hi,
Script should be as follow
EntityA_WellDescribed:
Load * form EntityA_WellDescribed ;
join
Load Name_NWF as Name , Code_NWF as Code From EntityA_NOT_WellDescribed;
-Peterson
To get the correct/desired result please Use below script
A:
LOAD
Name
,
Code
,
Address
FROM
EntityA_Welldefined.xlsx
(
ooxml, embedded labels, table is Sheet1);
B:
LOAD
Name_NWF
as Name,
Code_NWF
as Code,
'NA'
asAddress
FROM
EntityA_notWelldefined.xlsx
(
ooxml, embedded labels, table is Sheet1)
Where
not exists (Code,Code_NWF)
;
Will generate thecorrect result
'
Thank you Peterson, but the join duplicates the lines, since we do want to mark as non available "NA" fields that are not present on the NWF table.
Thanks again
ptspy
Great. Thanks a lot.
Worked perfectly.
best regards,
ptspy