How to show data that not existing in another table

[#1]

Dear guys,

Assuming I have 2 tables

table 1:

item	Description
001	Product 1
002	Product 2
003	Product 3
004	Product 4

table 2:

item	Description
001	Product 1
002	Product 2
005	Product 5
006	Product 6

Question is "how to recognize/show the item 005 & 006 as they don't exist in table1?"

Many thanks,

Dong

[#2]

Any one can help, please?

[sunny_talwar · Accepted solution]

Add Flags when you load them

Table:

LOAD item,

    Description,

    'Table1' as Flag

FROM

[https://community.qlik.com/thread/236561]

(html, codepage is 1252, embedded labels, table is @1);

Concatenate (Table)

LOAD item,

    Description,

    'Table2' as Flag

FROM

[https://community.qlik.com/thread/236561]

(html, codepage is 1252, embedded labels, table is @2)

Where not Exists(item);

[#4]

Hi Sunny,

Could you please check whether syntax error in my scripts? I don't know why error occurs

CheckCode:

LOAD

     [Distributor Code] as Code,

     'Existed' as Flag

FROM

[.\Master data for stock report.xlsx]

(ooxml, embedded labels, table is [SA-Distributors Mapping]);

Concatenate(CheckCode)

LOAD

     right(Item,6) as Code,

     'SA new' as Flag

FROM

[.\Daily Stocks\Daily Sanofi Stock.xlsx]

(ooxml, embedded labels, table is [Sheet1])

Where not exists(Code);

Error message:

Field not found - <Code>

Concatenate(CheckCode)

LOAD

     right(Item,6) as Code,

     'SA new' as Flag

FROM

[.\Daily Stocks\Daily Sanofi Stock.xlsx]

(ooxml, embedded labels, table is [Sheet1])

Where not exists(Code)

Many thanks

[Anonymous]

Hi Dong,

apply following script

CheckCode:

LOAD

     [Distributor Code] as Code,

     'Existed' as Flag

FROM

[.\Master data for stock report.xlsx]

(ooxml, embedded labels, table is [Master]);

Concatenate(CheckCode)

LOAD

     right(Item,6) as Code,

     'SA new' as Flag

FROM

[.\Stock.xlsx]

(ooxml, embedded labels, table is [Sheet1])

Where not exists(right(Item,6));

Thanks,

[tresesco]

Try:   Where not exists(Code, right(Item,6) );

[#7]

Hi,

The error is in below snippet (bold):

Concatenate(CheckCode)

LOAD

    right(Item,6) as Code,

    'SA new' as Flag

FROM

[.\Daily Stocks\Daily Sanofi Stock.xlsx]

(ooxml, embedded labels, table is [Sheet1])

Where not exists(Code);

Try this:

Concatenate(CheckCode)

LOAD

    right(Item,6) as Code,

    'SA new' as Flag

FROM

[.\Daily Stocks\Daily Sanofi Stock.xlsx]

(ooxml, embedded labels, table is [Sheet1])

Where not exists(Code,right(Item,6));

hope this helps.

[#8]

Many thanks Tresesco. I have solved my error.

However, I have to mark Sunny's answer is the correct one. Is it ok for you?

[sunny_talwar]

You can always reward multiple responses by marking helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful‌

Best,

Sunny

[tresesco]

It's good to be rewarded, but that's not the ultimate reason we are here for. I am happy that I could help.