Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

kpatelacmm
New Contributor

Lookup in Qlikview with adjustable variables

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Lookup in Qlikview with adjustable variables

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)

)


talk is cheap, supply exceeds demand
11 Replies
MVP & Luminary
MVP & Luminary

Re: Lookup in Qlikview with adjustable variables

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.


talk is cheap, supply exceeds demand
kpatelacmm
New Contributor

Re: Lookup in Qlikview with adjustable variables

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

MVP & Luminary
MVP & Luminary

Re: Lookup in Qlikview with adjustable variables

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)

)


talk is cheap, supply exceeds demand
kpatelacmm
New Contributor

Re: Lookup in Qlikview with adjustable variables

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

Krishan

kpatelacmm
New Contributor

Re: Lookup in Qlikview with adjustable variables

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

kpatelacmm
New Contributor

Re: Lookup in Qlikview with adjustable variables

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

MVP & Luminary
MVP & Luminary

Re: Re: Lookup in Qlikview with adjustable variables

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


talk is cheap, supply exceeds demand
Highlighted
kpatelacmm
New Contributor

Re: Lookup in Qlikview with adjustable variables

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

kpatelacmm
New Contributor

Re: Lookup in Qlikview with adjustable variables

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.