Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Partner
Partner

Avoid Duplicates with script after concatenation

Hi to all!!

On the next image,  a table that has added aditional info from another table by concatenation process. Importante to mention that the second table is going to be  Load further on.

I need to take away the duplicates after the concatenation.

Can someone help me with the script and steps to do so? If its possible I need to see the code as it has to be write in the script... I'm very new

 

Qlik.png

Labels (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: Avoid Duplicates with script after concatenation

My bad, it was a mistake in my previous code. 

 

[Ventas]:
LOAD
  Cliente as VenCodigoCliente,
  "Nombre 1" as VenNomCliente,
FROM 
   [lib://Dropbox - alejandro@inflexion.com.co (qlikcloud_qlikid_aholguininflex)/Zona K/Ventas e Inventarios/VentasI.XLSX]
(ooxml, embedded labels, table is Sheet1)
;

Concatenate (Ventas)
LOAD
  Code as VenCodigoCliente,
  Name as VenNomCliente
FROM 
  [lib://Clientes (qlikcloud_qlikid_aholguininflex)] (html, utf8, embedded labels, table is @1)
WHERE 
  NOT EXISTS(VenCodigoCliente, Code ) //Use Code not VenCodCliente as 2nd parameter
;

 

???
Celebrating my 300 accepted solution in the Qlik Community
???
6 Replies
Partner
Partner

Re: Avoid Duplicates with script after concatenation

Try something like this:

Ventas:
LOAD
...,
... AS VenMes,
... AS VenCodCliente,
... AS VenNomCliente
FROM [Dropbox Ventas Inventarios];

CONCATENATE (Ventas)
LOAD
VenCodCliente,
VenNomCliente
FROM
[Clientes]
WHERE
NOT EXISTS(VenCodCliente, VenCodCliente) //the key for not adding duplicates in the CONCATENATE.
;

(For future posts I would recommend you to add som sample code as text, not picture. Text is much easier to copy into an answer than than a picture, especially when answering from a mobile phone)

-Vegar
???
Celebrating my 300 accepted solution in the Qlik Community
???
Partner
Partner

Re: Avoid Duplicates with script after concatenation

Hi

It says the field does not exist...

 

I´m sending the text from the script, if you may helpmeet I really appreciate!!!!

[Ventas]:

LOAD
Cliente as VenCodigoCliente,
"Nombre 1" as VenNomCliente,

FROM [lib://Dropbox - alejandro@inflexion.com.co (qlikcloud_qlikid_aholguininflex)/Zona K/Ventas e Inventarios/VentasI.XLSX]
(ooxml, embedded labels, table is Sheet1);

Concatenate (Ventas)

LOAD
Code as VenCodCliente,
Name as VenNomCliente

FROM [lib://Clientes (qlikcloud_qlikid_aholguininflex)] (html, utf8, embedded labels, table is @1)
WHERE NOT EXISTS(VenCodigoCliente, VenNomCliente);

 

Captura de Pantalla 2019-05-07 a la(s) 2.20.56 p. m..png

bruno_silva100
New Contributor III

Re: Avoid Duplicates with script after concatenation

For you concatenate the table the fields should be equals.
sample
TABLE_1:
load
field1,
field2
from path;

concatenate

load
field3 as field1
field4 as field2
field4 as field3
from path;
If you try concatenate table with the fields diferents will go duplicate your result.
Partner
Partner

Re: Avoid Duplicates with script after concatenation

Bruno hi

I fix the mistake but unfortunately it still with out working... Any idea?

 

 

Captura de Pantalla 2019-05-07 a la(s) 2.43.21 p. m..png

bruno_silva100
New Contributor III

Re: Avoid Duplicates with script after concatenation

The fields in the tables are diferents. They need to be equals, or you can use the left join
Partner
Partner

Re: Avoid Duplicates with script after concatenation

My bad, it was a mistake in my previous code. 

 

[Ventas]:
LOAD
  Cliente as VenCodigoCliente,
  "Nombre 1" as VenNomCliente,
FROM 
   [lib://Dropbox - alejandro@inflexion.com.co (qlikcloud_qlikid_aholguininflex)/Zona K/Ventas e Inventarios/VentasI.XLSX]
(ooxml, embedded labels, table is Sheet1)
;

Concatenate (Ventas)
LOAD
  Code as VenCodigoCliente,
  Name as VenNomCliente
FROM 
  [lib://Clientes (qlikcloud_qlikid_aholguininflex)] (html, utf8, embedded labels, table is @1)
WHERE 
  NOT EXISTS(VenCodigoCliente, Code ) //Use Code not VenCodCliente as 2nd parameter
;

 

???
Celebrating my 300 accepted solution in the Qlik Community
???