Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
dcd123456
New Contributor III

exclude records from one table

Hello

We have a table that was loaded from an excel file

TAB1:

ZonaField1Field2Field3Num
A123usr11
B46usr21
C27usr31
D77usr21
E11usr11
F45usr51
G67usr21

we need exclude from the table TAB1 the records that have a value in Field3 that was in the table TAB2

TAB2:

Field3

usr1

the TAB2 also is loaded from an excel file.

any body know how i can exclude the records in Tab1 where the field3 = usr1 ???

thanks in advance for your help

regards

1 Solution

Accepted Solutions
Highlighted
pradeepreddy
Valued Contributor II

Re: exclude records from one table

try something like this...

Tab2:

LOAD Field3 AS FIELD3

FROM

Qlik_123921.xlsx

(ooxml, embedded labels, table is Tab2);

TAB1:

LOAD Zona,

     Field1,

     Field2,

     Field3,

     Num

FROM

Qlik_123921.xlsx

(ooxml, embedded labels, table is Tab1)

where not Exists(FIELD3,Field3);

Drop table Tab2;

Regards,

Pradeep

View solution in original post

11 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: exclude records from one table

Hi,

first load table TAB2, then load table TAB1 where not exists(Field3)..

- Ralf

Vizlib Head of R&D
Highlighted
vardhancse
Valued Contributor III

Re: exclude records from one table

hi first load tab2 then join/keep of tab2 for tab 1

Highlighted
dcd123456
New Contributor III

Re: Re: exclude records from one table

Hello

i have loaded the tble TAb2 first and add where not exists(Field3), but smething is wrong the system supress all records of usr1 (right) but also supress several records with the value usr2 and usr3, now


Field1Field2Field3Zona
27usr3C
45usr5F
46usr2B


it seem that there is only one record by value Field3.

!

Highlighted
dcd123456
New Contributor III

Re: exclude records from one table

hello

how i can do this "First load tab2 then join/keep of tab2 for tab 1"?? i try

Tab1:

inner keep (Tab2) LOAD Zona,

     Field1, ...

but don't work...

thanks

regards

Highlighted
MVP & Luminary
MVP & Luminary

Re: exclude records from one table

Ok, I see. You will need to use another function: lookup

Just post your script and I can adjust..

Vizlib Head of R&D
Highlighted
pradeepreddy
Valued Contributor II

Re: exclude records from one table

try something like this...

Tab2:

LOAD Field3 AS FIELD3

FROM

Qlik_123921.xlsx

(ooxml, embedded labels, table is Tab2);

TAB1:

LOAD Zona,

     Field1,

     Field2,

     Field3,

     Num

FROM

Qlik_123921.xlsx

(ooxml, embedded labels, table is Tab1)

where not Exists(FIELD3,Field3);

Drop table Tab2;

Regards,

Pradeep

View solution in original post

Highlighted
dcd123456
New Contributor III

Re: exclude records from one table

thanks, it works with ...where not Exists(FIELD3,Field3);..

regards

Highlighted
Not applicable

Re: exclude records from one table

Hi,

Please try like this,

QUALIFY Field3;

TAB1:

LOAD * INLINE [

    Zonal, Field1, Field2, Field3, Num

    A, 12, 3, usr1, 1

    B, 4, 6, usr2, 1

    C, 2, 7, usr3, 1

    D, 7, 7, usr2, 1

    E, 1, 1, usr1, 1

    F, 4, 5, usr5, 1

    G, 6, 7, usr2, 1

];

QUALIFY Field3;

TAB2:

LOAD * INLINE [

    Field1, Field2, Field3, Zonal

    2, 7, usr3, C

    4, 5, usr5, F

    4, 6, usr2, B

    6, 6, usr6, D

];

TAB3:

LOAD Field1 as F1, Field2 as F2,TAB2.Field3 as F3, Zonal as Z1 Resident TAB2

Where not Exists(TAB1.Field3,TAB2.Field3);

Regards,

Raja.

Highlighted
dcd123456
New Contributor III

Re: exclude records from one table

Hello

Could you explain how to use the lookup funciones, to do the same, the scrip is:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='#.##0,00 €;-#.##0,00 €';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';

SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';

SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

//

T2:

LOAD Field3

FROM

(ooxml, embedded labels, table is Hoja1);

T1:

LOAD Zona,

     Field1,

     Field2,

     Field3,

     Num

FROM

(ooxml, embedded labels, table is Hoja1)

Where not Exists(Field3);

thanks in advance

regards

dcd