Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Get Previous VALUE

Hello people, I am having problems by getting the previous value from a row.

1. I did create a calendar.

2. I did extract data from DB

3. I made a CROSS JOIN to have a qvd file a follow:

1.jpg

The marked rows are values I extratec from DB and left joined with the calendar.

I was using peek and previous formulas to create a qvd with the next structure, but I failed:

2.jpg

(The blank fields in column VALUE are null as well as the cell for jan-2015 should be in VALUE1 column)

Please, some help with ideas to solve this.

Thank you in advance.

1 Solution

Accepted Solutions
hirishv7
Not applicable

Re: Get Previous VALUE

Hi,

Check this,

Temp:

LOAD *,

RowNo() as S.no

INLINE [

    KPI, Year, Month, Value

    A, 2015, Jan

    A, 2015, Feb, 34

    A, 2015, Mar

    A, 2015, Apr, 55

    A, 2015, Jun

    A, 2016, Jan, 22

    A, 2016, Feb

    A, 2016, Mar, 29

    A, 2016, Apr

    A, 2016, Jun

];

Data:

LOAD *,

     If(Value, Value, Peek(Value1)) as Value1

Resident Temp Order By S.no Asc;

Drop table Temp;

As output,

Get Previous VALUE Peek-216839.PNG

HTH,

PFA,

Hirish

“Aspire to Inspire before we Expire!”
5 Replies
Gysbert_Wassenaar
Not applicable

Re: Get Previous VALUE

If the first screenshot is your source data then you can use this load statement:

LOAD

     KPI,

     YEAR,

     MONTH,

     VALUE,

     If(VALUE, VALUE, Peek(VALUE1) as VALUE1

FROM

     ...source...


talk is cheap, supply exceeds demand
swuehl
Not applicable

Re: Get Previous VALUE

Try something like

LOAD KPI, YEAR; MONTH, VALUE,

          If(Len(Trim(VALUE)), VALUE, Peek('VALUE1') ) AS VALUE1

RESIDENT YourTable

ORDER BY KPI,YEAR, MONTH

//here, take care that Month has a numeric representation, e.g. use QV Month() function to create your month values

;

Not applicable

Re: Get Previous VALUE

Hello,

Thank you for your help.

I was wondering what if I would have more than one KPI. how could I get he same structure.

3.jpg

I used your formula and it is working, but with more than one KPI is getting other's KPI values.

hirishv7
Not applicable

Re: Get Previous VALUE

Hi,

Check this,

Temp:

LOAD *,

RowNo() as S.no

INLINE [

    KPI, Year, Month, Value

    A, 2015, Jan

    A, 2015, Feb, 34

    A, 2015, Mar

    A, 2015, Apr, 55

    A, 2015, Jun

    A, 2016, Jan, 22

    A, 2016, Feb

    A, 2016, Mar, 29

    A, 2016, Apr

    A, 2016, Jun

];

Data:

LOAD *,

     If(Value, Value, Peek(Value1)) as Value1

Resident Temp Order By S.no Asc;

Drop table Temp;

As output,

Get Previous VALUE Peek-216839.PNG

HTH,

PFA,

Hirish

“Aspire to Inspire before we Expire!”
swuehl
Not applicable

Re: Get Previous VALUE

It should work with a proper sorting of the input table ( ORDER BY .. if needed) and an additional conditional in the script:

   If(Len(Trim(VALUE)), VALUE, If(KPI= Peek('KPI'), Peek('VALUE1')) ) AS VALUE1