Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Keeping only the first entry of a data set.

Hi!

I'm trying to minimize duplicates in a chart by only keeping the first entry for a product:

PRODUCTBAR CODE
A123
A456
A789
B101
B102
B103

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?

Tags (2)
9 Replies
Highlighted
Creator
Creator

Re: Keeping only the first entry of a data set.

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)

;

Highlighted

Re: Keeping only the first entry of a data set.

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

Highlighted
MVP
MVP

Re: Keeping only the first entry of a data set.

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

;

Highlighted
Creator III
Creator III

Re: Keeping only the first entry of a data set.

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.

Highlighted
Not applicable

Re: Keeping only the first entry of a data set.

I would prefer to do it in the script since I am working with a straight table.

Highlighted
Not applicable

Re: Keeping only the first entry of a data set.

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?

Highlighted
Not applicable

Re: Keeping only the first entry of a data set.

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?

Highlighted
Creator II
Creator II

Re: Keeping only the first entry of a data set.

Hello Andrea,

try this

LOAD Product,
Barcode
FROM
YOURSOURCE where not Exists(Product);

Best regards

Highlighted
MVP & Luminary
MVP & Luminary

Re: Keeping only the first entry of a data set.

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.