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

[#1]

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?

[sinanozdemir · Accepted solution]

Hi,

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

I am also attaching the qvw.

Thanks

[#3]

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.

[sinanozdemir]

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

[#5]

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

[sinanozdemir]

No problem.

Thanks