Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
I'm trying to minimize duplicates in a chart by only keeping the first entry for a product:
PRODUCT | BAR CODE |
---|---|
A | 123 |
A | 456 |
A | 789 |
B | 101 |
B | 102 |
B | 103 |
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?
Table1:
Load
Product,
Barcode
from yourtable
where not exists(Product)
;
Try this:
Table:
LOAD PRODUCT,
[BAR CODE]
FROM
[https://community.qlik.com/thread/159496]
(html, codepage is 1252, embedded labels, table is @1);
Table1:
NoConcatenate
LOAD PRODUCT,
FirstValue([BAR CODE]) as [BAR CODE]
Resident Table
Group By PRODUCT;
DROP Table Table;
Best,
Sunny
this
source:
LOAD PRODUCT,
[BAR CODE]
FROM
[https://community.qlik.com/thread/159496]
(html, codepage is 1252, embedded labels, table is @1);
final:
load *, if(peek(PRODUCT)<>PRODUCT, 1, 0) as FLAGFIRSTPRODUCT
Resident source
order by PRODUCT, [BAR CODE];
drop table source;
or this
final2:
LOAD PRODUCT as PRODUCT2,
min([BAR CODE]) as [BAR CODE 2]
FROM
[https://community.qlik.com/thread/159496]
(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.
Any suggestions?
Hello Andrea,
try this
LOAD Product,
Barcode
FROM
YOURSOURCE where not Exists(Product);
Best regards
Hi,
If you want to use Maxstring() you need to use Group By clause like below
Data:
Load
Product,
MaxString(Barcode) AS Barcode
FromDataSource
Group By Product;
Hope it helps you.
Regards,
Jagan.