1 Reply Latest reply: Sep 15, 2014 4:11 PM by Stefan Kunte RSS

    scripting

      hi Qv,

       

      Temp:

      LOAD Name,

           Date,

           Year(Date) as Year,

           Sales

      FROM

      Data.xlsx

      (ooxml, embedded labels, table is [Dummy Sales]);

       

      Temp1:

      NoConcatenate

      LOAD

      *

      Resident Temp

      Order by Name, Date;

       

      DROP Table Temp;

       

      CumulativeSum:

      NoConcatenate

      LOAD

           Name,

           Date,

           Year,

           Sales,  

           If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum    

      Resident Temp1;

       

      DROP Table Temp1;

      ---------------------------------------------------------------------------------

       

      i have done Cumulative sum for above and the problem is i need commission for Different names commision may vary how to give dynamic in set analysic plz help me

       

      data is like this how to do ,

       

      Name            Year             Start             End     Commission
      Peter20140600001.0%
      Peter2014600011000001.5%
      Peter20141000011500002.0%
      Peter20150500001.0%
      Peter2015500011000001.5%
      Peter20151000011500002.0%
      Tom20140500001.2%
      Tom2014500011000001.6%
      Tom20141000011500002.5%
      Tom20150500001.2%
      Tom2015500011000001.6%
      Tom20151000011500002.5%
      Mark20140500000.8%
      Mark201450001800001.4%
      Mark2014800011400002.0%
      Mark20150500000.8%
      Mark201550001800001.4%
      Mark2015800011400002.0%
        • Re: scripting
          Stefan Kunte

          hi,

           

          try this:

          CumulativeSum:

          NoConcatenate

          LOAD

              Name,

              Date,

              Year,

              Sales,

              Name & '|' & Year as %NameYear,

              If(Name <> Previous(Name) or Year <> Previous(Year), Sales, RangeSum(Sales, Peek('CumulativeSum'))) AS CumulativeSum  

          Resident Temp1;

           

          given your Table above then write:

           

          intervalls:

          Intervallmatch(CumulativeSum, %NameYear)

          load*

          ;

          load

          Start,

          End,

          Name & '|'  &  as %NameYear,

          Commission

          Resident myintervalltable; //this is the table you posted above

           

           

           

          left join (CumulativeSum)

          %NameYear,

          Commission

          Resident intervalls;

           

          drop table intervalls;

           

           

          This will however only work when you only have one Value for CumulativeSum per every Name/Year.

           

           

           

          Best

          Stefan