Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

ujjwalraja
New Contributor III

Logic for Last Consumption Price| QlikView

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Logic for Last Consumption Price| QlikView

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.

View solution in original post

4 Replies
ujjwalraja
New Contributor III

Re: Logic for Last Consumption Price| QlikView

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
Valued Contributor

Re: Logic for Last Consumption Price| QlikView

MVP
MVP

Re: Logic for Last Consumption Price| QlikView

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.

View solution in original post

captain89
Contributor

Re: Logic for Last Consumption Price| QlikView

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