Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
amarbogam
Contributor

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)

;

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

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

;

mrjohn23
Contributor 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.

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.

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?

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?

anlonghi2
Contributor 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

MVP
MVP

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.

Community Browser