Exclude duplicate records in load

[oscargm_bi]

Hi all, 

Reciently I found that the data I have been loaded to Qlik, have a duplicate records. I tried to delete it from the original source, the xsl file but was too slowly and did not work So now, im triying to make it in Qlik but I can not find the way to do it.

The record that is duplicate is the "Movimiento" and its something like:

X, Movimiento, Z

1, 1234, AMA 

2,12356, AMA

3,1234, AMA

4,16425, AMA

If u see, the code 1234 is repeating in the 1 and 3 and I need just one of its, no both.

This is the code from the data loader:

LOAD
"Fecha de Visado",
month("Fecha de Visado") AS MES_VISADO,
Year("Fecha de Visado") AS AÑO_VISADO,
Producto,
Asistencia,
Movimiento,
Importe,
Importe/3434 AS [Importe en Euro], // con precio a cierre de marzo.
Importe>300000 AS [MAYOR DE 300K],
"Estado ME",
WildMatch("Estado ME",'P','R','V'),
"Tipo ME",
"Tipo ME2",
"Anula Servicio",
Servicio,
Proveedor,
Cliente,
"Nombre provincia/Province name",
"Nombre localidad/Locality name",
"Fecha de alta del movimiento/Economic operation addition date and time",
Month("Fecha de alta del movimiento/Economic operation addition date and time") AS MES_MOV,
year("Fecha de alta del movimiento/Economic operation addition date and time") AS AÑO_MOV,
// AÑO,
// MES,
CONTRATO,
"Contrato a Refacturar"
FROM [lib://DS_Colombia/SINIESTROS\BASE LIQUIDADOS.xlsx]
(ooxml, embedded labels, table is BASE)
Where Match(Cliente, 'LIBERTY SEGUROS', 'ARP LIBERTY SEGUROS DE VIDA S.A.', 'LIBERTY SEGUROS DE VIDA S.A.') = 0;

Can anyone help me?

Thanks in advance,

Regards,

Oscar

[YoussefBelloum · Accepted solution]

[EDITED]

Hi,

I didn't see that you already had a WHERE clause

Now, you can try to add the red part below to your existing where

where Match(Cliente, 'LIBERTY SEGUROS', 'ARP LIBERTY SEGUROS DE VIDA S.A.', 'LIBERTY SEGUROS DE VIDA S.A.') = 0 and not(Exists(Movimiento));

[oscargm_bi]

That works !!! THANK YOU.

I was going to tell u that I had to delete the "where to match" for incluide the new one, but with that update works fine.



Thank u again!

[YoussefBelloum]

You're welcome.

based on the help of the function on help.qlik.com

Exists() determines whether a specific field value has already been loaded into the field in the data load script. The function returns TRUE or FALSE, so can be used in the where clause of a LOAD statement or an IF statement.

So, it returns true everytime it finds an already loaded Movimiento value, preceded with NOT in a Where clause, it filters the lines containing an already loaded Movimiento value..

[YoussefBelloum]

@oscargm_bi 

one last thing

you can replace your '=0' from the first Match with another NOT preceding that match.

Like this:

NOT Match(Cliente, 'LIBERTY SEGUROS', 'ARP LIBERTY SEGUROS DE VIDA S.A.', 'LIBERTY SEGUROS DE VIDA S.A.')

[oscargm_bi]

Ok, I already did it.

Thanks again 🙂