3 Replies Latest reply on Oct 9, 2018 7:42 AM by Vasu choudhury

    Lookup the values from other table..

    Vasu choudhury



      Can you please help me in resolving the below issues from the attached workbook. This is extension to the original request.

      Re: Lookup the value from other table


      Global Filters: State, City & Year.


      If year=2018-Q1 is selected then based on the Fact % value, it has to look up for year =2018 from lookup table. If year=2017-Q4 is selected then based on the Fact % value, it has to look up for year =2017 values from lookup table.



      1. Sum for M1 & M2 are not correct after joining with Lookup table. Fact % value was derived from Sum(M1)/Sum(M2) at each ID level.


      2. Also the logic to be implemented for few categories/ID's are different ex: 94: 0.04%. But when you look at the lookup table the min & max are reported in reverse order when compared with other ID's

      Food- 0.04%: 3=7.065......7 + ((0.04-0.01)/(0.49-0.01))

      Note: If the result of ((0.04-0.01)/(0.49-0.01)) >0.9 then it should display as 0.9 and not the actual result.

      Ex: 7+0.987 =7.987. If we round to 1 decimal then it will be 8.0. but expected results are 7.9


      3. For ID's=77, 48 & 61==> if SUM(M2) at ID level is 0 then 0 else 3 would suffice.

      We have additional of 2 columns in lookup table for identifying the ID's which has min & max values in Asc/Desc order(R_Flag)  and has single/multiple records(N_Flag) and see if it helps in implementing the logic bit easier.


      ID's=94/48 has min & max values in reverse order, hence you see R_Flag=Y. Whereas ID=94 has classification in the lookup table and hence N_Flag=Y, but ID=48 doesn't have classification(single row) and hence you see as N_Flag=N.

      ID=12/77/61 has min & max in Asc order, so R_Flag=N. Whereas ID's=77/61 doesn't have classification(single row) in the lookup table and hence N_Flag=N, but 12 has classification, so N_FLAG=Y.


      If there is a single record for that ID, then we set the value as 3 by default.



      4. For ID's=12,37,85 & 94==> If % of Fact Value falls in any of the below ranges then respective condition should be satisfied.

          a) if SUM(M2) at ID level is 0 then 0.

          b) if SUM(M2) at ID level is between 0 and 20 then 3.

          c) if SUM(M2) at ID level is >20 then relevant logic should be applied for each ID's i.e. For 94, point #2 logic should be implemented and for ID's 12, 37, 85, the logic which you have provided holds good.


      5. When Year=2018-Q1 is selected from quick filters(Fact table) then % of Fact value should be compared with 2018 from lookup table and when selected 2017-Q1      then % of Fact value should be calculated with year=2017.


      I hope it is clear. Please let me know for any questions.


      I have all these calculations implemented in a Function(Oracle). Created a calculated field with Live connection by using RAWSQLAGG_REAL but not matching exactly for some of the ID's or might have implemented it wrong. I believe extract will not work with these pass through results.Any thoughts on this approach?

      SELECT ID, F_FUNCTION(ID,sum(M1),sum(M2),sum(Met),sum(notmet),'2018-Q1') as points from Table A


      Note: % of Fact value will change based on State & City quick filter values. Based on the results, it should compare with Lookup table values for that time period.


      I hope it is clear and let me know for any questions.


      Attached updated workbook. Any help is highly appreciated.