It's difficult to handle data connections after extract.
that's the reason nobody try to challenge.
Could you attach original two excel files independently?
I think you should use join.
Thanks Shin for looking into it.
I tried joining between these 2 tables & pass-through functions(on lookup/bridge table), but didn't get expected results for some of the conditions. Attached the excel.
Grouping.xlsx 11.5 KB
If you have further questions, please reply to this post.
If the problem is solved, please mark my answer as Correct / Helped to close the thread, not from inbox but from original post.
Thanks for looking into and you are the master!!!
Sorry for taking some time to implement & validate some of the additional asks, but seems like I am having hard time with these many logic's.
1. Sum for M1 & M2 are not correct after joining with Lookup table.
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.
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.
Attached updated workbook. Any help is highly appreciated.
comlicated_Join.twbx 31.7 KB
Need some time.
Added another 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.
For point #3. For ID's=77, 48 & 61==> if SUM(M2) at ID level is 0 then 0 else 3 would suffice.
If there is a single record for that ID, then we set the value as 3 by default.
comlicated_Join.twbx 32.0 KB
I did take a look but have completely confused.
First of all, as I wrote, without original excel, I cannot investigate anything, please attach twbx WITHOUT extract data or excel itself..
And below was not at all at first post and no idea what is this sum
And it seems like you are using different data which is not available as original data of excel.
As a conclusion,
My wish is that you close this thread as original and post new with new data set and new issue.