Discussion Board for collaboration on QlikView Scripting.
I'm trying to minimize duplicates in a chart by only keeping the first entry for a product:
Because there are multiple barcodes for each product, it creates multiple entries - however we only need one for the current report I am working on.
How can I keep only the first entry for each product?
You can prevent loading these records in the script like below or do you want to do it in the front-end?
where not exists(Product)
(html, codepage is 1252, embedded labels, table is @1);
FirstValue([BAR CODE]) as [BAR CODE]
Group By PRODUCT;
DROP Table Table;
load *, if(peek(PRODUCT)<>PRODUCT, 1, 0) as FLAGFIRSTPRODUCT
order by PRODUCT, [BAR CODE];
drop table source;
LOAD PRODUCT as PRODUCT2,
min([BAR CODE]) as [BAR CODE 2]
(html, codepage is 1252, embedded labels, table is @1)
Group by PRODUCT
I had this problem but sovled this issue in my SQL query by using the MAX function. Or look into the first value syntax and add that into your qlikview script.
I would prefer to do it in the script since I am working with a straight table.
It keeps telling me that the expression is invalid, I'm not sure what I am doing wrong. Does this only work with certain versions?
The max function got me thinking about MaxString in Qlik, I tried that and it told me it was an invalid expression even though I can find examples of script using it.
LOAD Product, Barcode FROM YOURSOURCE where not Exists(Product);
If you want to use Maxstring() you need to use Group By clause like below
MaxString(Barcode) AS Barcode
Group By Product;
Hope it helps you.