exclude records from one table

[dcd123456]

Hello

We have a table that was loaded from an excel file

TAB1:

Zona	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

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

[rbecher]

Hi,

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

- Ralf

[vardhancse]

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

[dcd123456]

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

Field1	Field2	Field3	Zona
2	7	usr3	C
4	5	usr5	F
4	6	usr2	B

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

!

[dcd123456]

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

[rbecher]

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

Just post your script and I can adjust..

[PradeepReddy · Accepted solution]

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

[dcd123456]

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

regards

[#9]

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.

[dcd123456]

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