Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
udaya_kumar
Specialist
Specialist

Get the value of the previous rows loaded

Hi,

I have data like this

Month
YearValue
Jan201710
Feb201720
Mar201713
Apr201714
May201712
Jun201723
Jul201745
Aug2017676
Sep201778
Oct201765
Nov201755
Dec201777
Jan201888
Feb201844
Mar201833
Apr201821
May201834

The expected output is like the one below

Month
YearValue
Jan2017
Feb2017
Mar2017
Apr201710
May201720
Jun201713
Jul201714
Aug201712
Sep201723
Oct201745
Nov2017676
Dec201778
Jan201865
Feb201855
Mar201877
Apr201888
May201844

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?

7 Replies
sunny_talwar

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;


Capture.PNG

udaya_kumar
Specialist
Specialist
Author

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.

sunny_talwar

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

udaya_kumar
Specialist
Specialist
Author

Hi Sunny,

PFA the sample data for the same.

Thanks.

sunny_talwar

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

udaya_kumar
Specialist
Specialist
Author

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.

sunny_talwar

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