topic Loading “Complementary” records from 2 files in QlikView

Loading “Complementary” records from 2 files

Tue, 18 May 2010 11:05:03 GMT

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

Loading “Complementary” records from 2 files

Tue, 18 May 2010 11:15:22 GMT

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

Loading “Complementary” records from 2 files

Tue, 18 May 2010 11:15:54 GMT

To get the correct/desired result please Use below script

LOAD

Name

Code

Address

FROM

EntityA_Welldefined.xlsx

(

ooxml, embedded labels, table is Sheet1)

;

LOAD

Name_NWF

as Name

Code_NWF

as Code

'NA'

Address

FROM

EntityA_notWelldefined.xlsx

(

ooxml, embedded labels, table is Sheet1

)

Where

not exists (Code,Code_NWF

)

;

Will generate thecorrect result

Loading “Complementary” records from 2 files

Tue, 18 May 2010 14:02:33 GMT

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

Loading “Complementary” records from 2 files

Tue, 18 May 2010 14:04:47 GMT

Great. Thanks a lot.

Worked perfectly.

best regards,

ptspy