10 Replies Latest reply: Sep 10, 2012 7:12 AM by whiteline _ RSS

    Previus value in table

    Krzysztof Kurowski

      Hi

       

      I got a table with three columns (Date, Code, Level).

      Sometimes I getting null values in level column, when in level column is null I need replace to last value for specific code.

       

      Example below:

      Record 11/11/2011, K20 - is null the I need to get last value (sort by date) for K20 which is 150.

      table.jpg

      Please advise.

      Thanks

        • Re: Previus value in table
          Roberto Postma

          In the loadscript you can check for null values, and apply an if statement. In pseudo-code: if value isNull (in my code: len()<1) THEN get previousvalue, else, the current value

           

          code

          source:
          load *
           inline [
          Date,Code,Level
          10/11/2011,k10,50
          10/11/2011,k20,150
          11/11/2011,k10,60
          11/11/2011,k20,
          12/11/2011,k10,80
          12/11/2011,k20,200 
          ] 
          ;
          
          NoConcatenate
          destination:
          load 
          Date,Code,Level,
          if(len(Level)<1,previous(Level),Level) as previousColumn
          Resident source
          order by Code,Date;//note: the orderby (and sequence of orderby is important for which previous() value is picked...
          drop table source;
          
          

           

          See also attached for the example in a qvw.

           

          Hope it helps!