topic Re: Re: Populate a column with Where(not)exist in QlikView

Populate a column with Where(not)exist

Thu, 11 Sep 2014 15:06:21 GMT

Hello everyone, I have an issues here:

I have two tables

Table1:

customid,order

C100,10

C100,20

C101,10

C102,50

etc

Table2:

customid,status

C100, yes

C100,yes

C101,yes

etc

How can I join these 2 tables and add the value 'No' to the field 'status' for those customid who's not in table 2?

THanks a lot for your help!

Re: Populate a column with Where(not)exist

MK_QSL — Thu, 11 Sep 2014 15:17:19 GMT

Table1:

Load * Inline

[

customid, order

C100, 10

C100, 20

C101, 10

C102, 50

C103, 100

C104, 1000

];

Table2:

Load *, customid as CID Inline

[

customid, status

C100, yes

C101, yes

];

Concatenate

Load customid,'No' as status Resident Table1 Where Not Exists (CID,customid);

Drop Field CID;

Re: Populate a column with Where(not)exist

Thu, 11 Sep 2014 16:06:50 GMT

Thank you very much Manish! Can you explain why do we need to add a new field 'CID' to make it work? And is it possible to combine table1 and table2 into one table? Thanks again!

Re: Populate a column with Where(not)exist

MK_QSL — Thu, 11 Sep 2014 18:46:12 GMT

When we are using below is the syntax.

Where Not Exists(CurrentTableFieldName, FIeldNameToCompare);

So, for comparision, I have created CID which is not needed after the comparison, so being dropped/deleted..

if you want to join two tables, use below code after drop statement..

Left Join (Table1) Load * Resident Table2;

Drop Table Table2;

Re: Re: Populate a column with Where(not)exist

Thu, 11 Sep 2014 20:41:43 GMT

Your solution works perfectly! Thanks a lot!

Here I discovered something else: I have this file (see the attachment) ,

- when i rename the KeyField and use where Exists(Key Field_New name), the load wouldn't work and I would have the error message:Field not found - <custom>

- when I load with where Exists(Key Field_Old name), there's no error message but the field 'status' is empty

How can I make it work?

tab1:

LOAD customid as custom,

order

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD customid as custom,

status

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet2)

where Exists(custom);

Re: Re: Populate a column with Where(not)exist

MK_QSL — Fri, 12 Sep 2014 07:55:14 GMT

tab1:

LOAD customid,

order

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet1);

LOAD customid as custom,

status

FROM

Book2.xlsx

(ooxml, embedded labels, table is Sheet2)

where Exists(custom, customid);