2 Replies Latest reply: Mar 29, 2018 9:13 AM by Matt Milian RSS

    Loading Previous Data

    Matt Milian

      Hello,

       

      What I am trying to accomplish is to sum all amounts for each year based on field 1 and field 2, but if a year has no amount, to use the previous one that is available.

       

      Field1,Field2,Field3,Amount

      1,B,2015,50

      1,L,2015,75

      1,B,2016,0

      1,L,2016,0

      1,B,2017,100

      1,L,2017,125

      2,B,2015,90

      2,L,2015,120

      2,B,2016,0

      2,L,2016,0

      2,B,2017,0

      2,L,2017,0

      3,B,2015,90

      3,L,2015,120

      3,B,2016,40

      3,L,2016,80

      3,B,2017,0

      3,L,2017,0

        • Re: Loading Previous Data
          Sunny Talwar

          Try this

           

          Table:

          LOAD * INLINE [

              Field1, Field2, Field3, Amount

              1, B, 2015, 50

              1, L, 2015, 75

              1, B, 2016, 0

              1, L, 2016, 0

              1, B, 2017, 100

              1, L, 2017, 125

              2, B, 2015, 90

              2, L, 2015, 120

              2, B, 2016, 0

              2, L, 2016, 0

              2, B, 2017, 0

              2, L, 2017, 0

              3, B, 2015, 90

              3, L, 2015, 120

              3, B, 2016, 40

              3, L, 2016, 80

              3, B, 2017, 0

              3, L, 2017, 0

          ];


          FinalTable:

          LOAD *,

          If(Field1 = Previous(Field1) and Field2 = Previous(Field2) and Amount = 0, Peek('New_Amount'), Amount) as New_Amount

          Resident Table

          Order By Field1, Field2, Field3;


          DROP Table Table;

           

          Capture.PNG

          • Re: Loading Previous Data
            Matt Milian

            Thank you Sunny.  That works perfectly.