2 Replies Latest reply: Jul 30, 2012 10:05 AM by Stefano Peroni RSS

    How to transform a table

      Dear all,

      I'm newer and I'm facing the the following problem. I've a table like this:

       

      Supplier     Year      Month     Paid amount

      A               2012     01          1000

      A               2012     02          1500

      A               2012     03          800

      B               2012     01          5000    

      B               2012     02          2000

      B               2012     03          1500

      B               2012     04          1000

       

      where each line, represents the paid amount in the month (per year and supplier)

      I've to build a new table like this:

       

      Supplier     Year     Month     Total paid amount at the month

      A               2012     01          1000

      A               2012     02          2500 (1000 + 1500)

      A               2012     03          3300 (1000 + 1500 + 800)

      B               2012     01          5000

      B               2012     02          7000 (5000 + 2000)

      B               2012     03          8500 (5000 + 2000 + 1500)

      B               2012     04          9500 (5000 + 2000 + 1500 + 1000)

       

      where each line, contains the progressive paid amount. For example: for the supplier A, the second line contains the sum of  January (01) and February(02),

      1000 + 1500.

      The third line, is the contains the sum of 1000 + 1500 + 800 and so on

       

      I hope somebody can help me

       

      Thank in advance

       

        • Re: How to transform a table
          Stefan Wühl

          Hi peronist,

           

          you should ensure that your table is ordered appropriately, then you can use peek() function to retrieve the value of a field in a previous record (i.e. this allows you to "add up" values from to different record lines):

           

          INPUT:

          LOAD * INLINE [

          Supplier,     Year,      Month,     Paid amount

          A,               2012,     01,          1000

          A,               2012,     02,          1500

          A,               2012,     03,          800

          B,               2012,     01,          5000   

          B,               2012,     02,          2000

          B,               2012,     03,          1500

          B,               2012,     04,          1000

          ] ;

           

          FACT:

          LOAD *,

          rangesum([Paid amount],if(Supplier=peek(Supplier),peek(Total))) as Total

          Resident INPUT order by Supplier, Month;

           

          drop table INPUT;

           

          If you have multiple years, you may want to order by Supplier, Year, Month and also include a check for Year in the if() statement:

          rangesum([Paid amount],if(Supplier=peek(Supplier) and Year=peek(Year),peek(Total))) as Total


          Hope this helps,

          Stefan