topic Re: Translating Summary in Excel to an aggregated summary table in Qlikview? in App Development

Translating Summary in Excel to an aggregated summary table in Qlikview?

Mon, 01 Feb 2016 20:32:32 GMT

Case Number	Reason Denied 1- Missing Info	Reason Denied 2- Contracting Issue	Appeals Made	Appeals with response from insurance company	Appeals Won (Paid)	Total Paid
1	1	0	2	1	1	$60
2	1	1	1	0	0	$0
3	1	0	3	3	1	$100
4	0	1	1	1	1	$60

I have created a summary table in Excel with Pivot tables and a few functions, and am struggling how to create the same information in Qlikview. Please note: Denials are binary (1 means we were denied for this reason; 0 means denial was not for this reason); 1 denial can have 2 denial reasons (as shown in Case 2)

The final summary table looked like this:

Reason	Appeals Made	Appeals with Response	Appeals Won	Avg Paid per Appeal with Response
Reason 1 Denied- Missing Info	6	4	2	$40
Reason 2 Denied- Contracting Issue	2	1	1	$60

How can I create this in Qlikview?

Re: Translating Summary in Excel to an aggregated summary table in Qlikview?

sinanozdemir — Mon, 15 Feb 2016 16:27:42 GMT

Hi,

I divided the main table into two tables and then concatenated them:

I am also attaching the qvw.

Thanks

Re: Translating Summary in Excel to an aggregated summary table in Qlikview?

Tue, 23 Feb 2016 01:29:50 GMT

Hello Sinan,

Thank you for your reply. From where are you getting the 'Denied Reason 1' field? Is this supposed to be 'Reason Denied 1- Missing Info?' When I run the script, I get an error.

Re: Translating Summary in Excel to an aggregated summary table in Qlikview?

sinanozdemir — Wed, 24 Feb 2016 16:16:45 GMT

Hi,

Yes, "Denied Reason 1" field is "Reason Denied 1- Missing Info". I am actually taking your table and dividing it into two individual tables based on the denied reasons. Since all the columns are the same, QlikView is concatenating them into one table. In the last LOAD statement, I am eliminating the records that don't have relations to both with "Reason <> 0".

Maybe post your app so I can see what's going on.

Thanks

Re: Translating Summary in Excel to an aggregated summary table in Qlikview?

Wed, 24 Feb 2016 16:18:24 GMT

I got it to work yesterday, when I wrote it out. Thank you so much!

Re: Translating Summary in Excel to an aggregated summary table in Qlikview?

sinanozdemir — Wed, 24 Feb 2016 17:24:52 GMT

No problem.

Thanks