topic Re: Load Last item and price in QlikView

Load Last item and price

ecabanas — Mon, 18 Jan 2016 10:48:32 GMT

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

Re: Load Last item and price

sunny_talwar — Mon, 18 Jan 2016 10:51:10 GMT

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;

Re: Load Last item and price

ecabanas — Mon, 18 Jan 2016 10:58:41 GMT

Hi Sunny,

U did it again!!

Many thank's

Eduard

Re: Load Last item and price

sunny_talwar — Mon, 18 Jan 2016 10:59:18 GMT

Awesome