Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Loading “Complementary” records from 2 files

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

'



4 Replies
Not applicable

Loading “Complementary” records from 2 files

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

Loading “Complementary” records from 2 files

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

'



Highlighted
Not applicable

Loading “Complementary” records from 2 files

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

Loading “Complementary” records from 2 files

Great. Thanks a lot.

Worked perfectly.

best regards,

ptspy