Load only rows with min value

[#1]

Hi guys,

I'm trying to load "min values" from a table. I have redundant rows but I want only the row's which have the min Amount.

Example:

  ID, Amount

    1, 5

    1, 2

    2, 3

    2, 8

    3, 9

In this Example I want only back...

1,2

2,3

3,9

But I don't know to handle this.

Thank you

[Kushal_Chawda]

Data:

LOAD

ID

DateOne

SETDate

DateTwo,

floor(DateTwo)- floor(DateOne) as Diff

FROM table;

New:

LOAD ID,

           DateOne,

           FirstSortedValue(DateTwo,Diff) as DateTwo

Resident Data

group by ID,DateOne;

drop table Data;

Make sure that your Date fields are in proper format

[#3]

Thank you for that. There is only one thing missing. I need the SETDate in the New/Final Table...

[Kushal_Chawda]

Add the setdate in load and add it in group by also

[#5]

Oh sorry. I mean DateTwo

[Kushal_Chawda]

date2 is already there.. see firstsortedvalue

[tamilarasu]

Hi Kushal,

Sorry for the late reply. I started to home so couldn't reply.  I have misunderstood the question. Thanks for pointing my mistake.

[tamilarasu]

Hi Hans,

Try this,

Data:

LOAD ID,

     DateOne,

     SETDate,

     DateTwo,

     DateOne-DateTwo as Diff

FROM

(biff, embedded labels, table is Sheet1$);

Temp:

Inner Keep

Load ID,

DateOne,

Max(Diff) as Diff

Resident Data

Group by ID, DateOne;

DROP Table Temp;

[#9]

Now I see it's my fault. In the table there is a column missing -> Amount. I need Amount in my final table

[tamilarasu]

Try this,

Data:

LOAD ID,

     DateOne,

     SETDate,

     DateTwo,

     DateOne-DateTwo as Diff,

     Amount

FROM

(biff, embedded labels, table is Sheet1$);

Temp:

Inner Keep

Load ID,

DateOne,

Max(Diff) as Diff

Resident Data

Group by ID, DateOne;

DROP Table Temp;

[Kushal_Chawda]

Do below change in my above post

Add Amount field in Data table

Then add one more field amount in New table as below

firstsortedvalue(Amount,Diff) as Amount