topic Re: exclude records from one table in QlikView

exclude records from one table

dcd123456 — Tue, 01 Jul 2014 11:01:25 GMT

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

Re: exclude records from one table

rbecher — Tue, 01 Jul 2014 11:13:29 GMT

Hi,

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

- Ralf

Re: exclude records from one table

vardhancse — Tue, 01 Jul 2014 11:20:01 GMT

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

Re: Re: exclude records from one table

dcd123456 — Tue, 01 Jul 2014 11:22:08 GMT

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.

Re: exclude records from one table

dcd123456 — Tue, 01 Jul 2014 11:30:34 GMT

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

Re: exclude records from one table

rbecher — Tue, 01 Jul 2014 11:31:08 GMT

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

Just post your script and I can adjust..

Re: exclude records from one table

PradeepReddy — Tue, 01 Jul 2014 11:47:14 GMT

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

Re: exclude records from one table

dcd123456 — Tue, 01 Jul 2014 11:54:27 GMT

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

regards

Re: exclude records from one table

Tue, 01 Jul 2014 12:03:23 GMT

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.

Re: exclude records from one table

dcd123456 — Tue, 01 Jul 2014 14:34:21 GMT

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

Re: Re: exclude records from one table

rbecher — Tue, 01 Jul 2014 16:19:02 GMT

Here we go:

TAB2:
LOAD * INLINE [
    Field3
    usr1
];

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
]
Where IsNull(Lookup('Field3','Field3',Field3,'TAB2'));

- Ralf

Re: exclude records from one table

dcd123456 — Tue, 01 Jul 2014 16:24:03 GMT

thanks for your answer, it works.

regards

dcd