5 Replies Latest reply: May 13, 2016 1:31 PM by Stefan Wühl RSS

    Get Previous VALUE

    Diego Sandoval

      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.

        • Re: Get Previous VALUE
          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...

          • Re: Get Previous VALUE
            Stefan Wühl

            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
              Hirish V

              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