How to filter unwanted fields?

[#1]

Dear Experts,

     Let me ask you a question. I have two tables called Table1 & Table2. What I want to do is that I want to take out the fields from Table1 that are containing at the table2. The answer is Table3. What function is appropriate to get the right answer?

Thanks and Best Regards,

Kyaw Myo Tun

[qlikmsg4u · Accepted solution]

Try like this

Table2:

LOAD [Item Code.],

    [Buying Item]

FROM

[..\filter.xlsx]

(ooxml, embedded labels, table is Table2) ;

Table1:

LOAD Name,

    Id,

    [Buying Item],

    Amount

FROM

[..\filter.xlsx]

(ooxml, embedded labels, table is Table1)where not Exists ([Buying Item]) ;

drop table Table2;

[#3]

Dear QLIKmsg4u,

           It still showing that four rows in Table1. I want to take out two rows that containing in Table2(Buying Item). It should be like Table3:

Name	Id	Buying Item	Amount
Thet Htar	1113	Milk	800
Zaw Win	1114	Tea	300

Thanks,

Kyaw Myo Tun

[qlikmsg4u]

did you drop the table 2? it showing only 2 rows

[#5]

Here is my code,

Table2:

LOAD [Item Code.],

     [Buying Item]

FROM

C:\.........\filter.xlsx

(ooxml, embedded labels, table is Table2);

Table1:

LOAD Name,

     Id,

     [Buying Item],

     Amount

FROM

C:\.........\filter.xlsx

(ooxml, embedded labels, table is Table1);

drop table Table2;

Still showing this:

[qlikmsg4u]

You are missing where not Exists ([Buying Item]) in Table 2

Table2:

LOAD [Item Code.],

    [Buying Item]

FROM

[..\filter.xlsx]

(ooxml, embedded labels, table is Table2) ;

Table1:

LOAD Name,

    Id,

    [Buying Item],

    Amount

FROM

[..\filter.xlsx]

(ooxml, embedded labels, table is Table1) where not Exists ([Buying Item]) ;

see the bold text here

[#7]

Dear QLIKmsg4u,

       It works correctly now. I missed that where clause. Thanks for your help.

Thanks,

Kyaw Myo Tun

[qlikmsg4u]

Hi KyawMyoTun‌

Please close this thread if you got answer.

[#9]

Dear K N,

How to close the tread? I can only see "Helpful" in Actions.

Regards,

Kyaw Myo Tun

[awhitfield]

Hi kyaw,

you need to open from the main thread and you will see the option to mark an answer as CORRECT.

HTH - Andy