Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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.
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;
@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.
Rubenmarin,
Thank you for your fast response and helping me with my question. Have a great day!
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