Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to filter unwanted fields?

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

1 Solution

Accepted Solutions
qlikmsg4u
Specialist
Specialist

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;

View solution in original post

9 Replies
qlikmsg4u
Specialist
Specialist

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;

Not applicable
Author

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:

    

NameIdBuying ItemAmount
Thet Htar1113Milk800
Zaw Win1114Tea300

Thanks,

Kyaw Myo Tun

qlikmsg4u
Specialist
Specialist

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

Untitled.png

Not applicable
Author

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
Specialist
Specialist

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

Not applicable
Author

Dear QLIKmsg4u,

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

Thanks,

Kyaw Myo Tun

qlikmsg4u
Specialist
Specialist

Hi KyawMyoTun

Please close this thread if you got answer.

Not applicable
Author

Dear K N,

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

Regards,

Kyaw Myo Tun

awhitfield
Partner - Champion
Partner - Champion

Hi kyaw,

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

HTH - Andy