topic Re: Lookup in Qlikview with adjustable variables in QlikView

Lookup in Qlikview with adjustable variables

kpatelacmm — Tue, 04 Feb 2014 00:36:59 GMT

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 — Tue, 04 Feb 2014 11:42:38 GMT

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

kpatelacmm — Tue, 04 Feb 2014 17:45:55 GMT

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 — Tue, 04 Feb 2014 18:03:43 GMT

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

kpatelacmm — Tue, 04 Feb 2014 18:08:08 GMT

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

Krishan

Re: Lookup in Qlikview with adjustable variables

kpatelacmm — Tue, 04 Feb 2014 20:28:25 GMT

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

kpatelacmm — Tue, 11 Feb 2014 20:08:55 GMT

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 — Tue, 11 Feb 2014 20:52:38 GMT

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

Re: Lookup in Qlikview with adjustable variables

kpatelacmm — Tue, 11 Feb 2014 23:10:00 GMT

Hi,

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

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

kpatelacmm — Thu, 13 Feb 2014 17:02:54 GMT

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 — Thu, 13 Feb 2014 17:28:37 GMT

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

kpatelacmm — Thu, 13 Feb 2014 18:56:43 GMT

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>