Remove Duplicate CustomerID and show only Customer with single Stock#.

[Dante_83]

Hello Qlik Community,

I'm confident that many of you can help me answer my question. Below is an example of my data. Notice that CustomerID: 334 and 966 are duplicates that order multiple parts. I want to show only CustomerID with a single Stock# on a table and hide CustomerID with multiple orders. Please help, and thank you in advance.

Department	CustomerID	Stock#	Part Desc
D	960	A1	Hammer
D	961	A2	Nails
D	962	B1	Drill Set
D	334	A1	Hammer
D	334	B3	Screw
D	334	C4	Power Saw
D	701	D1	Scrapper
D	702	D1	Scrapper
D	703	F1	Battery
D	999	B1	Drill Set
D	966	A1	Hammer
D	966	A2	Nails
D	966	D1	Scrapper
D	966	F1	Battery
D	966	C4	Power Saw
D	966	B3	Screw

[rubenmarin · Accepted solution]

Hi, in a table with those fields as dimensions you can change CustomerID to a calculated dimension as:

=Aggr(If(Count(DISTINCT Stock#)=1, CustomerID),CustomerID)

And uncheck the option to show null values.

[Luis4]

Hola Dante_83

Realize lo solicitado en el script del siguiente modo,usando la tabla que proporcionaste.

tabla_datos:
LOAD * INLINE [
Departamento, Identificacion, Existencias, Descripción
D, 960, A1, Martillo
D, 961, A2, Clavos
D, 962, B1, Juego de brocas
D, 334, A1, Martillo
D, 334, B3, Tornillo
D, 334, C4, Sierra mecánica
D, 701, D1, Peleón
D, 702, D1, Peleón
D, 703, F1, Batería
D, 999, B1, Juego de brocas
D, 966, A1, Martillo
D, 966, A2, Clavos
D, 966, D1, Peleón
D, 966, F1, Batería
D, 966, C4, Sierra mecánica
D, 966, B3, Tornillo
];

repetidos_identificacion:
load * where Repeticiones = 1 ;
LOAD
Departamento,
Identificacion,
Existencias,
Descripción,
Sum(If(Identificacion <> Previous(Identificacion), 1, 0)) as Repeticiones
Resident tabla_datos
Group By Departamento, Identificacion, Existencias, Descripción;

DROP Table tabla_datos;

[sidhiq91]

@Dante_83  You can also perform the same at the script level as below.

NoConcatenate
Temp:
Load * inline [
Department, CustomerID, Stock#, Part Desc
D, 960, A1, Hammer
D, 961, A2, Nails
D, 962, B1, Drill Set
D, 334, A1, Hammer
D, 334, B3, Screw
D, 334, C4, Power Saw
D, 701, D1, Scrapper
D, 702, D1, Scrapper
D, 703, F1, Battery
D, 999, B1, Drill Set
D, 966, A1, Hammer
D, 966, A2, Nails
D, 966, D1, Scrapper
D, 966, F1, Battery
D, 966, C4, Power Saw
D, 966, B3, Screw

];

inner join (Temp)
Temp1:
Load CustomerID
where Total_Customer=1;

Load Count(CustomerID) as Total_Customer,CustomerID
Resident Temp
group by CustomerID;

//Drop table Temp;

Exit Script;

If this resolves your issue, please like and accept it as a solution.

[Dante_83]

Rubenmarin,

Thank you for your fast response and helping me with my question. Have a great day!

[Dante_83]

Sidhiq91,

I use script sparingly. But I do want to learn more about it. Based on the data I provided, I'm only showing 7 Items (Part Desc), and on my actual data, I have over 100. If I write a script, do I have to list all 100 items to get my result? I will soon have to learn it, and I need a preview understanding before encountering that subject. Thank you.

Dante