Skip to main content
Announcements
Meet the 2024 Qlik Luminaries and Partner Ambassadors! READ ON!
cancel
Showing results for 
Search instead for 
Did you mean: 
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
HirisH_V7
Master
Master

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

HirisH
“Aspire to Inspire before we Expire!”

View solution in original post

5 Replies
Gysbert_Wassenaar

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
MVP
MVP

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
Author

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.

HirisH_V7
Master
Master

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

HirisH
“Aspire to Inspire before we Expire!”
swuehl
MVP
MVP

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