Populate a column with Where(not)exist

[#1]

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!

[MK_QSL · Accepted solution]

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

  C100, yes

  C101, yes

];

Concatenate

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

Drop Field CID;

[#3]

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!

[MK_QSL]

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;

[#5]

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);

[MK_QSL]

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);