Obtaining most recent price record

[#1]

Hi

I would like to find the most recent cost price from a table that holds date, item code, price.

I have written the following script to do this, but wondered if there was a quicker or more elegant way to do this

Thanks

Andy

PRICES_1:

load * inline [

date, stock item, price

01/01/2013, STK001, 10

01/01/2013, STK001, 13

01/06/2013, STK001, 12

04/09/2013, STK001, 14

];

PRICES:

load

  [stock item] & '|' & date(date) as [stock item date],

  [stock item],

  date,

  price

resident

  PRICES_1;

MAX_PRICE_1:

Load

  max(date(date)) as max_date,

  [stock item]

Resident

  PRICES

group by

  [stock item];

MAX_PRICE:

Load

  [stock item] & '|' & date(max_date) as [stock item date]

Resident

  MAX_PRICE_1;

left join(MAX_PRICE)

load

  [stock item date],

  [stock item],

  price as gross_cost_price

resident

  PRICES;

drop table PRICES, PRICES_1, MAX_PRICE_1;

[tresesco]

You might want to explore FisrtSortedvalue() function. Like:

FirstSortedValue(PRICES, -DateField)

[#3]

Thank you, that seems to work

[sadickbasha]

Below logic also an easier way to do

PRICES_1:

load * inline [

date, stock item, price

01/01/2013, STK001, 10

01/01/2013, STK001, 13

01/06/2013, STK001, 12

04/09/2013, STK001, 14

];

PRICE:

Stock Item,

Price,

Max(num(date)) as date

Resident PRICES_1

Group by

Stock Item,

Price;

[#5]

Thank you for the reply Sadick, I have gone for the first answer and that works fine.

I will have a look at your solution as well.