Load Last item and price

[ecabanas]

Hi Guys,

I load table with a lot of items, cost and dates:

ItemId                    date                Cost

A                       30/11/15                 1

A                       31/12/15                  1.15

B                       30/06/15                 2

B                       31/12/15                  2.15

I would like to:

WAC_Last:

Load Distinct ItemId,

Cost,

Max(Date) as Date_Last

resident WAC Group by ItemId;

But did not works, anyone how to solve this?

Many thank's Guys

Eduard

[sunny_talwar · Accepted solution]

Try this:

WAC_Last:

Load ItemId,

      FirstSortedValue(Cost, -Date) as Cost,

      Max(Date) as Date_Last

Resident WAC

Group by ItemId;

Updated code:

WAC:

LOAD * Inline [

ItemId,                    Date,               Cost

A,                       30/11/15,                 1

A,                       31/12/15,                  1.15

B,                       30/06/15,                 2

B,                       31/12/15,                  2.15

];

WAC_LAST:

LOAD ItemId,

     FirstSortedValue(Cost, -Date) as Cost,

     Date(Max(Date)) as Date_Last

Resident WAC

Group by ItemId;

DROP Table WAC;

[ecabanas]

Hi Sunny,

U did it again!!

Many thank's

Eduard

[sunny_talwar]

Awesome