Removing duplicates on load

[#1]

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!!

[Gysbert_Wassenaar]

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;

[#3 · Accepted solution]

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)

[#4]

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

[#5]

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;