Get Previous VALUE

[#1]

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.

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

[swuehl]

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

;

[#4]

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.

[HirisH_V7 · Accepted solution]

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

[swuehl]

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