topic Obtaining most recent price record in QlikView

Obtaining most recent price record

Thu, 07 Nov 2013 08:00:09 GMT

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;

Re: Obtaining most recent price record

tresB — Thu, 07 Nov 2013 08:07:02 GMT

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

FirstSortedValue(PRICES, -DateField)

Re: Obtaining most recent price record

Thu, 07 Nov 2013 08:40:11 GMT

Thank you, that seems to work

Re: Obtaining most recent price record

sadickbasha — Thu, 07 Nov 2013 08:54:04 GMT

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;

Re: Obtaining most recent price record

Wed, 20 Nov 2013 08:05:24 GMT

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.