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

# Lookup the values from other table..

Hello,

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

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.

Ex:

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.

• ###### 1. Re: Lookup the values from other table..

Hi Vasu,

I am providing #1, but as to #2 on-ward, you added too much complicated logic.

Unfortunately now this reached a threshold of my capability to support as form activity.

It may do-able, but just too heavy-duty to handle in such free forum, please understand.

Thanks,

Shin

• ###### 2. Re: Lookup the values from other table..

Appreciate for your effort and looking into the issue.

Yeah!!! it has very complex logic and need to implement with all permutations & combinations which I am working on it parallel.

Please look into it when your time permits and keep posted if i have any solution for this.

Thanks once again!!

• ###### 3. Re: Lookup the values from other table..

Hi Shin,

I think we are back to square one!!!!

If i join the fact table with this look up table, the data is getting blow up to 3 billions. Is there any other alternative approach either in dashboard logic or data model creation to address this requirement.