11 Replies Latest reply: Feb 13, 2014 1:56 PM by Krishan Patel RSS

    Lookup in Qlikview with adjustable variables

    Krishan Patel

      I am trying to have qlikview do some kind of a lookup for a specific value but in a specific order. I also need qlikview to be able to do this in real time without having to reload the document.

       

       

      This is an example of the starting point.  I have a list of 27,577 categories and based on priority I need to look up all the diagnosis_codes and in the Capturesource flag put what the source is. So first it will go to the claims and look up all the diagnosis_code’s and which ever matches it will flag that line as CLAIM then the lookup goes to priority 2 which is GFPCPO and looks up and flags those lines etc through all the priorities. I need to be able to adjust the priorities freely. So if we decide claim is going to be priortity 2 and GFPCPO to priority 1 then I need to adjust something in qlikview manually and then it should recalculate and we should get new numbers withou reloading any data.

       

      Hcc Risk

      HCC

      CaptureSource

      Rank

      diagnosis_code

      HCC52^

      52

      CLAIM

      1-CLAIM

      304.01

      HCC131^

      131

      CLAIM

      1-CLAIM

      403.91

      HCC131^

      131

      CLAIM

      1-CLAIM

      585.2

      HCC19^

      19

      GFPCPO

      2-GFPCPO

      250

      HCC19^

      19

      GFPCPO

      2-GFPCPO

      250.02

       

      This is an example of what will happen if claim is priority 1 and GFPCPO is 2. I have others as well that are priotized but i am only using the first two in the example.Those can be added once the logic is working.

      Unique

      1

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Count of MRN_HCC

      Column Labels

       

       

       

       

       

       

       

       

       

      Row Labels

      1-CLAIM

      2-GFPCPO

      3-GFPCPN

      4-GFSpec

      5-HRAPCP

      6-HRAAVP

      7-CAPCP

      8-CASpec

      9-CAHOSP

      #N/A

      Grand Total

      NO

                            58

                     261

                        83

                          4

                          4

                        16

                        15

                          2

       

                  5,437

                     5,880

      YES

                   17,400

                  2,505

                     777

                        20

                        61

                     235

                     292

                        37

                        31

                     339

                  21,697

      Grand Total

                   17,458

                  2,766

                     860

                        24

                        65

                     251

                     307

                        39

                        31

                  5,776

                  27,577

       

        • Re: Lookup in Qlikview with adjustable variables
          Gysbert Wassenaar

          I think you should concatenate some tables and add a sources table with an input field to allow users to change the priority of the sources. See attached qvw.

            • Re: Lookup in Qlikview with adjustable variables
              Krishan Patel

              This is really close to what I need. The only thing is i am unable to get to the 17,400 for the claims and 2,505 for the GFPCPO when claims is priorty 1 and GF is priority 2.

               

              Of the 27,000 records only 17,400 match but qlikview is showing 23667?

               

              Any ideas?

              Krishan

                • Re: Lookup in Qlikview with adjustable variables
                  Gysbert Wassenaar

                  Try this expression instead:

                   

                  pick(match(SOURCE,'CLAIM','GFPCPO')

                  ,count({<MRN_HCC=e({<Priority={'<$(=only({<SOURCE={"CLAIM"}>}Priority))'}>} MRN_HCC)>}distinct MRN_HCC)

                  ,count({<MRN_HCC=e({<Priority={'<$(=only({<SOURCE={"GFPCPO"}>}Priority))'}>} MRN_HCC)>}distinct MRN_HCC)

                  )

                    • Re: Lookup in Qlikview with adjustable variables
                      Krishan Patel

                      Wow it works. Really appreciate this!!!! Thank you so much.

                       

                      Krishan

                      • Re: Lookup in Qlikview with adjustable variables
                        Krishan Patel

                        One piece i am missing. I am able to see by priority anything that matches. The ones  that dont match are not in an priorty category so they dont show up anywhere.

                         

                        For example:

                        Total = 27577

                        Matched via one of the priorities = 21801

                        Unmatched via one of the priorities = 5776 - This is the number that is missing? Is there a way to put the rest of them in a category so i can see that number somehow?

                         

                        Thanks,

                        Krishan

                          • Re: Lookup in Qlikview with adjustable variables
                            Krishan Patel

                            Hi,

                             

                            I am trying to add an element that will allow me to get costs for a specific provider. I tried to add another table with the provider name and the source but i am getting a message saying the data is looping and it is not working.

                             

                            Krishan

                            • Re: Re: Lookup in Qlikview with adjustable variables
                              Gysbert Wassenaar

                              You can try replacing the nulls in the SOURCE field with real values. See attached qvw.

                                • Re: Lookup in Qlikview with adjustable variables
                                  Krishan Patel

                                  Hi,

                                   

                                  Thank you. I will try that. Another question below.

                                   

                                  I am trying to add an element that will allow me to get costs for a specific provider. I tried to add another table with the provider name and the source but i am getting a message saying the data is looping and it is not working.

                                   

                                  I am getting a looping error.

                                   

                                  I have the main data and the key is the MRN_HCC. Then i have all the sources in "OtherStuff" with the Source. In my main data set i have the providername and so i need to be able to select a provider and be able to see his costs for all the sources accordingly. When i add a table which links the providername i am getting the looping error.

                                   

                                  Krishan

                                  • Re: Lookup in Qlikview with adjustable variables
                                    Krishan Patel

                                    Hi Gysbert,

                                     

                                    I really appreciate the help with my QlikView questions? I posted another question regarding the loop error I am getting. Do you have any ideas regarding that?

                                     

                                    Thank you,

                                    Krishan

                                     

                                    Krishan Patel | Senior Financial Analyst

                                    AppleCare Medical Management

                                    6131 Orangethorpe Ave, Ste 280 | Buena Park, CA 90620

                                    direct: 714-452-5087 | facsimile: 714-443-4540

                                    Krishan.Patel@applecaremedical.com<mailto:Krishan.Patel@applecaremedical.com>

                                     

                                    This electronic message transmission, including any attachments, contains information from AppleCare Medical Management, which may be confidential or privileged.  The information is intended to be for the use of the individual or entity named above.  If you are not the intended recipient, be aware that any disclosure, copying, distribution or uses of the contents of this information is prohibited

                                     

                                    If you have received this electronic transmission in error, please notify the sender immediately by calling AppleCare Medical Management at 714 443-4500 or sending a "reply to sender only" fax to 714 443-4540. Please destroy all electronic and hard copies of the communication, including attachments.

                                      • Re: Lookup in Qlikview with adjustable variables
                                        Gysbert Wassenaar

                                        Yes, I do have an idea. Post the script that causes the loop. My clairvoyance module broke down earlier this week so can't read your mind at the moment to find out what you're doing in your qlikview document that causes the loop/

                                          • Re: Lookup in Qlikview with adjustable variables
                                            Krishan Patel

                                            Hi Gysbert,

                                             

                                            I have attached a copy which is causing the loop error.

                                             

                                            What I am trying to do is I need to be able to add a cost by source and by providername. Overall everything works at a high level or at the source level. If I pick providername all the numbers change and everything works. There is one piece which is the cost of each source by providername which I need to add so when I do pick a providername the cost reflects that providername by the appropriate source. I have added another table which has the source, provider name and cost, but that's where the loop comes into play.

                                             

                                            Thank you,

                                            Krishan

                                             

                                            Krishan Patel | Senior Financial Analyst

                                            AppleCare Medical Management

                                            6131 Orangethorpe Ave, Ste 280 | Buena Park, CA 90620

                                            direct: 714-452-5087 | facsimile: 714-443-4540

                                            Krishan.Patel@applecaremedical.com<mailto:Krishan.Patel@applecaremedical.com>

                                             

                                            This electronic message transmission, including any attachments, contains information from AppleCare Medical Management, which may be confidential or privileged.  The information is intended to be for the use of the individual or entity named above.  If you are not the intended recipient, be aware that any disclosure, copying, distribution or uses of the contents of this information is prohibited

                                             

                                            If you have received this electronic transmission in error, please notify the sender immediately by calling AppleCare Medical Management at 714 443-4500 or sending a "reply to sender only" fax to 714 443-4540. Please destroy all electronic and hard copies of the communication, including attachments.