2 Replies Latest reply: Nov 17, 2016 5:55 AM by Johan Bengtsson RSS

    Spread total amount over multiple produkt groups

    Johan Bengtsson

      Hello,

      I need some help before I go nuts :-)

       

      I'm having one table with fields StoreID, Country, Main group and Total amount:

      Table 1

      StoreIDCountryMain groupTotal amount

      1

      SELadies10000
      1SEMen5000
      2DELadies15000
      2DEMen7500


      And another table with fields Country, Main group, Item group and Share %:

      Table 2

      CountryMain groupItem groupShare %
      SELadies160
      SELadies240
      SEMen1075
      SEMen1125
      DELadies165
      DELadies235
      DEMen1055
      DEMen1145

       

      Now's the tricky part; I'd like to take each row in Table 1 and spread the total amount to occurring item group in Table 2 where field Country and Main group matches (Total Amount x Share %). So f.e. the first row in Table 1 should be split into 2 rows depending on there's 2 different item groups in country "SE" and Main group "Ladies".

       

      So new Table 3 should have fields Store ID, Country, Main group, Item group and new field Amount:

       

      Store IDCountryMain groupItem groupAmount
      1SELadies16000
      1SELadies24000
      1SEMen103750
      1SEMen111250
      2DELadies19750
      2DELadies25250
      2DEMen104125
      2DEMen113375