Logic for Last Consumption Price| QlikView

[ujjwalraja]

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

[ujjwalraja]

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;

[tomasz_tru]

This might be handy:

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

[tresesco · Accepted solution]

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.

[captain89]

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