topic Re: Removing dups with not exists in QlikView

Removing dups with not exists

Anonymous — Mon, 27 Apr 2015 22:42:25 GMT

Hi,

I have a resident table where I add new rows and I want to control duplicates by a key.

-- I load my resdiente table

Ventas:

LOAD mes,

ciudad,

producto,

venta

FROM

(ooxml, embedded labels, table is Hoja1);

-- I remove dups by key: mes, ciudad, producto

Ventas2:

LOAD

mes & ciudad & producto as key,

mes,ciudad,producto,

sum(venta) AS venta

Resident Ventas

Group by mes, ciudad, producto;

DROP TABLE Ventas;

-- I load new data of other excel and I want to control dups. I use not Exists sentence.

LOAD

mes & ciudad & producto as key,

mes, ciudad, producto,venta

FROM

(ooxml, embedded labels, table is Hoja1)

WHERE NOT Exists(mes & ciudad & producto) ;

It doesn't work, because it doesn't add any new row.

What is wrong?

Thanks

Re: Removing dups with not exists

maxgro — Mon, 27 Apr 2015 22:47:36 GMT

I think

WHERE NOT Exists(key, mes & ciudad & producto) ;

Re: Removing dups with not exists

Anonymous — Mon, 27 Apr 2015 22:48:50 GMT

It should be

where not exists (key, mes & ciudad & producto)

Re: Removing dups with not exists

Anonymous — Mon, 27 Apr 2015 23:01:01 GMT

Ok, It works, but I don't understand why

always two fields in not exists(field1, field2)

Is not posssible one field?

I try:

where not exists(key)

and it doesn' t work neither

Thanks

Re: Removing dups with not exists

Anonymous — Mon, 27 Apr 2015 23:08:25 GMT

It is possible with one field, if the field exists in the current table and in a table loaded before. In your case, the field key exists in an earlier table, Ventas2, but not in Hoja1.

Here is an example with one field:

Table1:

LOAD

FROM...;

Table2:

LOAD

FROM...

WHERE exists (A);

Because A is in both tables.

Example with two fields:

Table1:

LOAD

FROM...;

Table2:

LOAD

X as A,

FROM...

WHERE exists (A,X);

Because you only rename X to A in the second table, but there is no A field in the source of the second table.

One more note: the first parameter in exists(Param1, Param2) must always be a field. The second could be an expression as in your case.