5 Replies Latest reply: Dec 19, 2017 4:42 AM by Sunny Talwar RSS

    How to sum up the value of two rows in one table during data transform

    Yong Han

      Hi, I have a very tricky table like below the green part shows. So when flag is one, the real value of Budge in each month is actually value of budge in current month sum up the budge in January. Anyone can help me achieve the data like in yellow part shows?

       

      Thanks a lot!

         

      cost center yearmonthflagbudge_typebudge Budget_Final
      A201811ADR6565
      A201821ADR267
      A201831ADR166
      A201841ADR065
      A201851ADR368
      A201861ADR-461
      A201871ADR065
      A201881ADR267
      A201891ADR-263
      A2018101ADR-164
      A2018111ADR065
      A2018121ADR-362
      B201910Revenue10001000
      B201920Revenue10051005
      B201930Revenue10091009
      B201940Revenue10031003
      B201950Revenue10051005
      B201960Revenue10001000
      B201970Revenue10021002
      B201980Revenue10301030
      B201990Revenue10401040
      B2019100Revenue10501050
      B2019110Revenue10201020
      B2019120Revenue10001000
      B201911ADR7070
      B201921ADR272
      B201931ADR171
      B201941ADR070
      B201951ADR373
      B201961ADR-466
      B201971ADR070
      B201981ADR272
      B201991ADR-268
      B2019101ADR-169
      B2019111ADR070
      B2019121ADR-367
        • Re: How to sum up the value of two rows in one table during data transform
          Sunny Talwar

          Try this

           

          Table:

          LOAD * INLINE [

              cost center, year, month, flag, budge_type, budge

              A, 2018, 1, 1, ADR, 65

              A, 2018, 2, 1, ADR, 2

              A, 2018, 3, 1, ADR, 1

              A, 2018, 4, 1, ADR, 0

              A, 2018, 5, 1, ADR, 3

              A, 2018, 6, 1, ADR, -4

              A, 2018, 7, 1, ADR, 0

              A, 2018, 8, 1, ADR, 2

              A, 2018, 9, 1, ADR, -2

              A, 2018, 10, 1, ADR, -1

              A, 2018, 11, 1, ADR, 0

              A, 2018, 12, 1, ADR, -3

              B, 2019, 1, 0, Revenue, 1000

              B, 2019, 2, 0, Revenue, 1005

              B, 2019, 3, 0, Revenue, 1009

              B, 2019, 4, 0, Revenue, 1003

              B, 2019, 5, 0, Revenue, 1005

              B, 2019, 6, 0, Revenue, 1000

              B, 2019, 7, 0, Revenue, 1002

              B, 2019, 8, 0, Revenue, 1030

              B, 2019, 9, 0, Revenue, 1040

              B, 2019, 10, 0, Revenue, 1050

              B, 2019, 11, 0, Revenue, 1020

              B, 2019, 12, 0, Revenue, 1000

              B, 2019, 1, 1, ADR, 70

              B, 2019, 2, 1, ADR, 2

              B, 2019, 3, 1, ADR, 1

              B, 2019, 4, 1, ADR, 0

              B, 2019, 5, 1, ADR, 3

              B, 2019, 6, 1, ADR, -4

              B, 2019, 7, 1, ADR, 0

              B, 2019, 8, 1, ADR, 2

              B, 2019, 9, 1, ADR, -2

              B, 2019, 10, 1, ADR, -1

              B, 2019, 11, 1, ADR, 0

              B, 2019, 12, 1, ADR, -3

          ];


          FinalTable:

          LOAD *,

          If([cost center] = Previous([cost center]) and year  = Previous(year) and budge_type = Previous(budge_type), RangeSum(Peek('Budget_Final'), budge), budge) as Budget_Final

          Resident Table

          Order By [cost center], budge_type, year, month;


          DROP Table Table;

            • Re: How to sum up the value of two rows in one table during data transform
              Yong Han

              Hi,

              Very helpful, thanks a lot! But not exactly what I need. RangeSum can not apply, because final budget is sum of only current month and Month 1. For example for year 2018 and cost center A, final budget is 68 instead of 71.

               

              Br,

              Yong

                • Re: How to sum up the value of two rows in one table during data transform
                  Sunny Talwar

                  Got it, try this

                   

                  Table:

                  LOAD * INLINE [

                      cost center, year, month, flag, budge_type, budge

                      A, 2018, 1, 1, ADR, 65

                      A, 2018, 2, 1, ADR, 2

                      A, 2018, 3, 1, ADR, 1

                      A, 2018, 4, 1, ADR, 0

                      A, 2018, 5, 1, ADR, 3

                      A, 2018, 6, 1, ADR, -4

                      A, 2018, 7, 1, ADR, 0

                      A, 2018, 8, 1, ADR, 2

                      A, 2018, 9, 1, ADR, -2

                      A, 2018, 10, 1, ADR, -1

                      A, 2018, 11, 1, ADR, 0

                      A, 2018, 12, 1, ADR, -3

                      B, 2019, 1, 0, Revenue, 1000

                      B, 2019, 2, 0, Revenue, 1005

                      B, 2019, 3, 0, Revenue, 1009

                      B, 2019, 4, 0, Revenue, 1003

                      B, 2019, 5, 0, Revenue, 1005

                      B, 2019, 6, 0, Revenue, 1000

                      B, 2019, 7, 0, Revenue, 1002

                      B, 2019, 8, 0, Revenue, 1030

                      B, 2019, 9, 0, Revenue, 1040

                      B, 2019, 10, 0, Revenue, 1050

                      B, 2019, 11, 0, Revenue, 1020

                      B, 2019, 12, 0, Revenue, 1000

                      B, 2019, 1, 1, ADR, 70

                      B, 2019, 2, 1, ADR, 2

                      B, 2019, 3, 1, ADR, 1

                      B, 2019, 4, 1, ADR, 0

                      B, 2019, 5, 1, ADR, 3

                      B, 2019, 6, 1, ADR, -4

                      B, 2019, 7, 1, ADR, 0

                      B, 2019, 8, 1, ADR, 2

                      B, 2019, 9, 1, ADR, -2

                      B, 2019, 10, 1, ADR, -1

                      B, 2019, 11, 1, ADR, 0

                      B, 2019, 12, 1, ADR, -3

                  ];

                   

                  Left Join (Table)

                  LOAD [cost center],

                  budge_type,

                  year,

                  FirstSortedValue(budge, month) as MinDateBudge

                  Resident Table

                  Group By [cost center], budge_type, year;

                   

                  FinalTable:

                  LOAD *,

                  MinDateBudge- budge as Budget_Final

                  Resident Table;

                   

                  DROP Table Table;

                   

                  Capture.PNG

              • Re: How to sum up the value of two rows in one table during data transform
                susant Kumar swain

                May be try somethihg like

                 

                sum({<Month={"1"},flag={"1"}>} total<Year> Budge) + Sum(Budge)