topic Re: Logic for Last Consumption Price| QlikView in QlikView

Logic for Last Consumption Price| QlikView

ujjwalraja — Thu, 13 Sep 2018 07:32:31 GMT

Hi Qlik Team,

I have data as following:-

Item, TransactionDate, ConsumptionCost

1, 01-Aug-2018, 0

1, 15-Aug-2018, 25

1, 27-Aug-2018, 0

1, 27-Aug-2018, 84

2, 1-July-2018, 10

2, 10-July-2018, 0

2, 10-July-2018, 30

Now, I want to capture Last ConsumptionCost for each Item.

Our output should be

Item, TransactionDate, ConsumptionCost

1, 27-Aug-2018, 84

2, 10-July-2018, 30

Please help me out in this and let me know me in case of doubt.

Thanks

Re: Logic for Last Consumption Price| QlikView

ujjwalraja — Thu, 13 Sep 2018 07:50:06 GMT

My appoach

T1:

Item, TransactionDate, ConsumptionCost

1, 01-Aug-2018, 0

1, 15-Aug-2018, 25

1, 27-Aug-2018, 0

1, 27-Aug-2018, 84

2, 1-July-2018, 10

2, 10-July-2018, 0

2, 10-July-2018, 30

T2:

Load Item, max(TransactionDate) as TransactionDate

resident T1

group by Item;

inner join(T1)

Load Item,

TransactionDate

resident T2;

Drop table T2;

Re: Logic for Last Consumption Price| QlikView

tomasz_tru — Thu, 13 Sep 2018 07:50:39 GMT

This might be handy:

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/aggr.htm

Re: Logic for Last Consumption Price| QlikView

tresB — Thu, 13 Sep 2018 07:54:49 GMT

You can get the same in the UI easily using FirstSortedValue(), like:

Chart Dim: Item

Exp1: Max(TransactionDate)

Exp2: FirstSortedValue(ConsumptionCost, -TransactionDate)

or, you can use the same function in the script.

Re: Logic for Last Consumption Price| QlikView

captain89 — Thu, 13 Sep 2018 09:55:19 GMT

Hi,

anyway i suggest to make a rank culumn directly in the script in this way:

T1:

LOAD *,

recno() as id,

date(Date#(TransactionDate, 'DD-MMM-YYYY')) as Date

INLINE [

Item,TransactionDate,ConsumptionCost

1,01-Aug-2018,0

1,15-Aug-2018,25

1,27-Aug-2018,0

1,27-Aug-2018,84

2,1-Jul-2018,10

2,10-Jul-2018,0

2,10-Jul-2018,30

];

T2:

load *,

if (Previous(Item) = Item, Peek("rank")+1 , 1) as rank

resident T1 order by Item, Date desc;

drop table T1;

You can select only non-zero values using a where clause:

T1:

LOAD *,

recno() as id,

date(Date#(TransactionDate, 'DD-MMM-YYYY')) as Date

INLINE [

Item,TransactionDate,ConsumptionCost

1,01-Aug-2018,0

1,15-Aug-2018,25

1,27-Aug-2018,0

1,27-Aug-2018,84

2,1-Jul-2018,10

2,10-Jul-2018,0

2,10-Jul-2018,30

] where ConsumptionCost>0;

T2:

load *,

if (Previous(Item) = Item, Peek("rank")+1, 1) as rank

resident T1 order by Item, Date desc;

drop table T1;

Bye