10 Replies Latest reply on Oct 4, 2018 2:25 PM by Vasu choudhury

    Lookup the value from other table

    Vasu choudhury

      Hello,

       

      I have a requirement where I get a % value from Fact table & look for the value in other bridge/lookup table and display final results with some calculation with State & City Filters dynamically as mentioned below.

       

      Expected Results:

      Logic to derive the output is look for period +(%value-min(current row value)/(max(next row value)-current row value))

      Auto - 75.17%: 9.786928 = 9+((75.17-63.13)/(78.43-63.13))

      Papers- 29.69%: 4.587924 = 4+((29.69-24.14)/(33.58-24.14))  and so on..

      Electronics - 66.04%: 3=3(since 66.04% doesn't fall in any of the existing range, then it should take the period value available in the next period as default) + 0

       

      When City= Lakewood then

      Expected Results:

      Electronics - 72.73% : 3=3(since 72.73% doesn't fall in any of the existing range, then it should take the period value available in the next period as default) + 0

      Papers- 16%: 3.173604 = 3+((16-14.29)/(24.14-14.29))

      Please let me know if any one has any solution on this.

        • 1. Re: Lookup the value from other table
          Vasu choudhury

          Experts,

          Is this achievable? or any other workaround..please!!

           

          Jonathan Drummey I see few blogs on RAWSQL. Can it be done with this? please advise.

          Shinichiro Murakami Is there a way I can tweak my data model and get the expected results? Please suggest if you have any.

          • 2. Re: Lookup the value from other table
            Shinichiro Murakami

            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.

             

            Shin

            • 3. Re: Lookup the value from other table
              Vasu choudhury

              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.

              • 4. Re: Lookup the value from other table
                Shinichiro Murakami

                Results

                 

                 

                Method

                 

                 

                 

                 

                 

                 

                 

                 

                 

                If this helped, please mark my answer as correct / helped to close the thread, not from inbox but from original post.

                 

                Thanks,

                Shin

                • 5. Re: Lookup the value from other table
                  Shinichiro Murakami

                  Hi Vasu choudhury

                   

                  Following up.

                  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,

                  Shin

                  • 6. Re: Lookup the value from other table
                    Vasu choudhury

                    Hi Shin,

                     

                    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.

                    Issues are:

                    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.

                     

                    Regards!

                    • 7. Re: Lookup the value from other table
                      Shinichiro Murakami

                      Need some time.

                       

                      Shin

                      • 8. Re: Lookup the value from other table
                        Vasu choudhury

                        Sure Shin.

                         

                        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.

                        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.

                         

                        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.

                        • 9. Re: Lookup the value from other table
                          Shinichiro Murakami

                          HI Vasu,

                           

                          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.

                           

                          Thanks,

                          Shin

                          • 10. Re: Lookup the value from other table
                            Vasu choudhury

                            Shinichiro Murakami

                             

                            As suggested, i have closed this thread and opened a new one with the changes/issues. Please take a look.

                            Lookup the values from other table..