Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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
Valued Contributor

Re: How to filter unwanted fields?

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;

9 Replies
qlikmsg4u
Valued Contributor

Re: How to filter unwanted fields?

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

Re: How to filter unwanted fields?

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
Valued Contributor

Re: How to filter unwanted fields?

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

Untitled.png

Not applicable

Re: How to filter unwanted fields?

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
Valued Contributor

Re: How to filter unwanted fields?

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

Re: How to filter unwanted fields?

Dear QLIKmsg4u,

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

Thanks,

Kyaw Myo Tun

qlikmsg4u
Valued Contributor

Re: How to filter unwanted fields?

Hi KyawMyoTun

Please close this thread if you got answer.

Not applicable

Re: How to filter unwanted fields?

Dear K N,

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

Regards,

Kyaw Myo Tun

awhitfield
Esteemed Contributor

Re: How to filter unwanted fields?

Hi kyaw,

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

HTH - Andy

Community Browser