1 Reply Latest reply: Nov 13, 2011 8:17 PM by Erica Whalley RSS

    How to create aggregation on script?

    Leandro Duarte

      Hi everybody!

       

      I'm trying to create an aggregation condition on script.

       

      Basically, I want to check what is the last month with volume greater than 0 considering the total per Supplier independentelly of the material involved.

       

      I need to group per supplier and check if the volume is greater to zero to consider as YTD, if false consider as NOTVALID month for calculations. It is needed to give a reference check and give a reference for the month (YTD or not)

       

      I have the following example:

       

      MonthSupplierProduct
      Value
      Jan-11A1200
      Jan-11A2150
      Jan-11B1130
      Feb-11B1200
      Feb-11A1250
      Mar-11A2200
      Apr-11A1350
      Mar-11B10
      Apr-11B10
      May-11A10

       

      In this example, supplier A last month is is Apr/2011 and supplier B last month is Feb/2011

       

      I really appreciate if somebody can give a support

       

      Thanks in advance,

      Leandro Duarte

        • Re: How to create aggregation on script?
          Hi Leandro
          I understand you want the latest month where there was a value greater than 0, for each supplier, in the script?
          My way to solve this would be to do a resident load, grouped by supplier that takes the maximum month where the value > 0, then join it onto the original table with a "dummy" field that acts as a flag.
          ie:
          left join (supp_tab)
          load max(Month) as Month,Suppler, 1 as latest_flag resident supp_tab where Value>0 group by Supplier;
          This would result in the same table, just with an extra field "latest_flag" which marks those months as being the latest.
          Regards,
          Erica