4 Replies Latest reply: Oct 21, 2017 5:04 PM by Max Shevchenko RSS

    get Aggr Sum per each row

    Max Shevchenko

      Hi,

       

      data:

      LOAD * Inline [

      Group, Subgroup, Date, Value, Value2

      Group1, Subgroup1, 01/01/2017, 100, 2

      Group1, Subgroup1, 01/02/2017, 0, 3

      Group1, Subgroup1, 01/03/2017, 200, 2

      Group1, Subgroup1, 01/04/2017, 0, 1

      Group2, Subgroup1, 01/05/2017, 100, 4

      Group2, Subgroup1, 01/06/2017, 0, 2

      Group2, Subgroup1, 01/07/2017, 0, 1

      ]

       

      I need to get the result as in table2. But I would like to show some logic in table1.

      Any suggestion is welcome.

       

      table1

      GroupSubgroupDateValueRepeatValueValue2Multiply( Sum(Value2)*RepeatValue )
      Group1Subgroup101/01/20171001002200
      Group1Subgroup101/02/201701003300
      Group1Subgroup101/0/3/20172002002400
      Group1Subgroup101/04/201702001200
      Group2Subgroup101/05/20171001004400
      Group2Subgroup101/06/201701002200
      Group2Subgroup101/07/201701001100

       

      table2

      GroupSum(Multiply)
      Group11100
      Group2700
        • Re: get Aggr Sum per each row
          Stefan Wühl

          Create the RepeatValue field in the script:

           

          SET DateFormat = 'MM/DD/YYYY';

           

          INPUT:

          LOAD * Inline [

          Group, Subgroup, Date, Value, Value2

          Group1, Subgroup1, 01/01/2017, 100, 2

          Group1, Subgroup1, 01/02/2017, 0, 3

          Group1, Subgroup1, 01/03/2017, 200, 2

          Group1, Subgroup1, 01/04/2017, 0, 1

          Group2, Subgroup1, 01/05/2017, 100, 4

          Group2, Subgroup1, 01/06/2017, 0, 2

          Group2, Subgroup1, 01/07/2017, 0, 1

          ];

           

          RESULT:

          LOAD *,

          If(Value = 0 and Peek(Group)=Group and Peek(Subgroup)=Subgroup, Peek(RepeatValue),Value) as RepeatValue

          Resident INPUT

          ORDER BY Group, Subgroup, Date asc;

           

           

          DROP TABLE INPUT;

           

          Then use the field in your chart expression

           

          Group Sum(RepeatValue*Value2)
          1800
          Group11100
          Group2700
            • Re: get Aggr Sum per each row
              Max Shevchenko

              Stefan, thanks for solution.

              Would you please advise how to make the same in case where two different tables?

              Script below doesn't provide an expected result.

               

              INPUT1:
              LOAD * Inline [
              Group, Subgroup, Date, Value
              Group1, Subgroup1, 01/01/2017, 2
              Group1, Subgroup1, 01/02/2017, 3
              Group1, Subgroup1, 01/03/2017, 2
              Group1, Subgroup1, 01/04/2017, 1
              Group2, Subgroup1, 01/05/2017, 4
              Group2, Subgroup1, 01/06/2017, 2
              Group2, Subgroup1, 01/07/2017, 1
              ];

              Left Join(INPUT1)
              INPUT2:
              LOAD * Inline [
              Group, Subgroup, Date, Value2
              Group1, Subgroup1, 01/01/2017, 100

              Group1, Subgroup1, 01/03/2017, 200
              Group2, Subgroup1, 01/05/2017, 100
              ];

              RESULT:
              LOAD *,
              If(Value2 = 0 and Peek(Group)=Group and Peek(Subgroup)=Subgroup, Peek(RepeatValue2),Value2) as RepeatValue2
              Resident INPUT1
              ORDER BY Group, Subgroup, Date asc;

              DROP TABLE INPUT1;

                • Re: get Aggr Sum per each row
                  Stefan Wühl

                  When checking if the value of Value2 is a valid one, you need to consider that due to the JOIN, you need to check for NULL, not zero.

                   

                  RESULT:

                  LOAD *,

                  If(Len(trim(Value2))=0 and Peek(Group)=Group and Peek(Subgroup)=Subgroup, Peek(RepeatValue2),Value2) as RepeatValue2

                  Resident INPUT1

                  ORDER BY Group, Subgroup, Date asc;

              • Re: get Aggr Sum per each row
                Max Shevchenko

                It works. Thank you very much!