2 Replies Latest reply: Dec 10, 2012 7:47 AM by 961405 RSS

    Complex Calculation using sparse lookup

    961405
      Hello -

      I am trying to design a model such that it would give me similar result as the query below

      select s.Prod_type, s.prod_code, ROUND(sum(cp.qty*(select cif.factor from index_factor cif
      where cif.factor_type = 'G'
                     and cif.category = s.prod_type
      and cif.type_code = s.prod_code
                     and cif.colour_code = s.prod_col))
      * 100 /sum(cp.qty) , 0 )
      from sale cp, product s where cp.product_id = s.product_id


      So, in the RPD, I created a sparse lookup table to obtain the factor (col F). Created a
      column A = Qty with aggregation as sum,
      column B = Col F * col A
      Column C = 100*Col B/Col A


      In the analysis, I pull the prod type, prodcode, col A, ColB, col C, col F. When I do the aggregation at prodcode, keeping the aggregation rule as default in all the columns, at the totals I would have expected to see in
      Col C= 100*sum(Col B)/sum(col A),
      instead what I get is, sum(Col A) * the factor of the min colour code * 100 / sum(col A).

      note: 1 prod type would have many prod code and one prod code would have many colours.

      Is there any way to achieve as expected.

      Thanks !!

      Edited by: 958402 on 29-Nov-2012 6:43 AM