topic Re: Get the value of the previous rows loaded in QlikView

Get the value of the previous rows loaded

udaya_kumar — Mon, 30 Jul 2018 17:34:24 GMT

Hi,

I have data like this

Month	Year	Value
Jan	2017	10
Feb	2017	20
Mar	2017	13
Apr	2017	14
May	2017	12
Jun	2017	23
Jul	2017	45
Aug	2017	676
Sep	2017	78
Oct	2017	65
Nov	2017	55
Dec	2017	77
Jan	2018	88
Feb	2018	44
Mar	2018	33
Apr	2018	21
May	2018	34

The expected output is like the one below

Month	Year	Value
Jan	2017
Feb	2017
Mar	2017
Apr	2017	10
May	2017	20
Jun	2017	13
Jul	2017	14
Aug	2017	12
Sep	2017	23
Oct	2017	45
Nov	2017	676
Dec	2017	78
Jan	2018	65
Feb	2018	55
Mar	2018	77
Apr	2018	88
May	2018	44

I want the value of Feb 2018 in May 2018 month, Jan 2018 value in Apr 2018 month, Dec 2017 Value in Mar 2018 and so on.

How can we achieve this functionality in QlikView?

Re: Get the value of the previous rows loaded

sunny_talwar — Mon, 30 Jul 2018 17:53:52 GMT

This?

Table:

LOAD *,

Date(MonthStart(Date#(Month&'-'&Year, 'MMM-YYYY')), 'MMM-YYYY') as MonthYear;

LOAD * INLINE [

Month, Year, Value

Jan, 2017, 10

Feb, 2017, 20

Mar, 2017, 13

Apr, 2017, 14

May, 2017, 12

Jun, 2017, 23

Jul, 2017, 45

Aug, 2017, 676

Sep, 2017, 78

Oct, 2017, 65

Nov, 2017, 55

Dec, 2017, 77

Jan, 2018, 88

Feb, 2018, 44

Mar, 2018, 33

Apr, 2018, 21

May, 2018, 34

];

FinalTable:

LOAD *,

If(RowNo() > 3, Peek('Value', -3)) as New_Value

Resident Table

Order By MonthYear;

DROP Table Table;

Re: Get the value of the previous rows loaded

udaya_kumar — Tue, 31 Jul 2018 11:10:08 GMT

Thanks for the reply Sunny.

What if we have multiple fields in our data and we have multiple entries for a single month. The above logic works only if we have single entry for a month and year.

So the Month Jan and Year 2018 repeats multiple times in a single table because of the combination of other fields in the table. Then i think we cannot use Peek this way.

Re: Get the value of the previous rows loaded

sunny_talwar — Tue, 31 Jul 2018 11:12:27 GMT

It might help to see what you might have... I am not sure we can solve if I keep on guessing what you might have

Re: Get the value of the previous rows loaded

udaya_kumar — Tue, 31 Jul 2018 12:48:18 GMT

Hi Sunny,

PFA the sample data for the same.

Thanks.

Re: Get the value of the previous rows loaded

sunny_talwar — Tue, 31 Jul 2018 12:52:11 GMT

And how would you like to see the new_value column? after loading this into your dashboard?

Re: Get the value of the previous rows loaded

udaya_kumar — Tue, 31 Jul 2018 13:05:11 GMT

I would like to see it as

Jan 2017, Feb 2017, Mar 2017,.... May 2018 will all have individual sum value.

But these months sum value should filter the data in front end based on other field selections in the table.

Like when we filter something from Hierarchy2 field, then the sum value for months should change based on the selection. If we filter on Hierarchy3 field, then the sum value for the months should change on fly.

Re: Get the value of the previous rows loaded

sunny_talwar — Tue, 31 Jul 2018 13:07:34 GMT

From what you are saying it might make sense to implement The As-Of Table