topic Re: Get Previous VALUE in QlikView

Get Previous VALUE

Fri, 13 May 2016 15:52:48 GMT

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:

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:

(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.

Re: Get Previous VALUE

Gysbert_Wassenaar — Fri, 13 May 2016 16:25:41 GMT

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

Re: Get Previous VALUE

swuehl — Fri, 13 May 2016 16:26:52 GMT

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

;

Re: Get Previous VALUE

Fri, 13 May 2016 16:52:22 GMT

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.

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

Re: Get Previous VALUE

HirisH_V7 — Fri, 13 May 2016 17:31:21 GMT

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,

HTH,

PFA,

Hirish

Re: Get Previous VALUE

swuehl — Fri, 13 May 2016 17:31:49 GMT

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