Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think
WHERE NOT Exists(key, mes & ciudad & producto) ;
It should be
where not exists (key, mes & ciudad & producto)
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
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.