Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading “Complementary” records from 2 files

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

1 Solution

Accepted Solutions
Not applicable
Author

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'

as

Address

FROM

EntityA_notWelldefined.xlsx

(

ooxml, embedded labels, table is Sheet1

)

Where

not exists (Code,Code_NWF

)

;

Will generate thecorrect result

'



View solution in original post

4 Replies
Not applicable
Author

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 Geeked



Not applicable
Author

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'

as

Address

FROM

EntityA_notWelldefined.xlsx

(

ooxml, embedded labels, table is Sheet1

)

Where

not exists (Code,Code_NWF

)

;

Will generate thecorrect result

'



Not applicable
Author

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

Not applicable
Author

Great. Thanks a lot.

Worked perfectly.

best regards,

ptspy