topic Re: Removing duplicates on load in QlikView

Removing duplicates on load

Fri, 24 May 2013 13:26:23 GMT

I need help. I have a table that houses a product name, a delivery date and other information. Lets use this for an example. This would be my table.

Product	Delivery Date	Description
1	June 1	Carton
1	June 2	Box
2	June 3	Material
3	June 4	Screws
1	June 5	Nails

I want to load my table where it only shows distinct items for the Product column. Essentially I need it to load only showing one item from the "Product" column showing the soonest delivery date and remove all duplicate lines bases on the Product column. Once it's loaded I'll create a chart and it should look like this,

Product	Delivery Date	Description
1	June 1	Carton
2	June 3	Material
3	June 4	Screws

Thanks for the help!!

Re: Removing duplicates on load

Gysbert_Wassenaar — Fri, 24 May 2013 13:32:34 GMT

If your source table is already sorted by date you can use this:

Load Product, [Delivery Date], Description

from somewhere

where not exists Product;

Alternatively you can try this:

load

Product,

min([Delivery Date]) as [Delivery Date],

firstsortedvalue(Description, [Delivery Date]) as Description

from somewhere

group by Product;

Re: Removing duplicates on load

Fri, 24 May 2013 13:34:39 GMT

I would do somthing like that :

LOAD

Product

minstring(Delivery Date) AS Delivery Date

minstring(Description) AS Description

Group by Product

From YourSource;

(Check the minstring function actually works, I have never used it)

Re: Removing duplicates on load

Fri, 24 May 2013 14:13:43 GMT

try this

load Product ,

min(date(date#([Delivery Date],'MMM D'),'MMM D')) AS [Delivery Date],

FirstSortedValue(Description,date(date#([Delivery Date],'MMM D'),'MMM D') )AS Description1

group by Product;

tab1:

LOAD Distinct * INLINE [

Product, Delivery Date, Description

1, June 1, Carton

1, June 2, Box

2, June 3, Material

3, June 4, Screws

1, June 5, Nails

];

then output like this

Product	Description1	[Delivery Date]
1	Carton	Jun 1
2	Material	Jun 3
3	Screws	Jun 4

Re: Removing duplicates on load

Fri, 24 May 2013 17:01:09 GMT

Thanks guys. This is the final code that worked for me.

LOAD Text3,

minstring(Start_Date) as Start_Date2

FROM

(biff, embedded labels, table is Task_Table1$)

//where Text3 = 'CARC00912';

group by Text3;