Removing dups with not exists

[Anonymous]

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

[maxgro]

I think

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

[Anonymous · Accepted solution]

It should be

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

[Anonymous]

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

[Anonymous]

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

A,

B

FROM...;

Table2:

LOAD

A,

C,

FROM...

WHERE exists (A);

Because A is in both tables.

Example with two fields:

Table1:

LOAD

A,

B

FROM...;

Table2:

LOAD

X as A,

C,

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.