Keeping only the first entry of a data set.

[#1]

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?

[Anonymous]

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)

;

[sunny_talwar]

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

[maxgro]

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

;

[Anonymous]

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.

[#6]

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

[#7]

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?

[#8]

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]

Hello Andrea,

try this

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

Best regards

[jagan]

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.