topic Re: IF Condition in QlikView

IF Condition

Fri, 21 Oct 2016 20:58:17 GMT

Hello Everyone,

I have a calculated dimension with below if condition.

Dimension:

=if(Denial_Code_Experimental = 1, 'Experimental',

if(Denial_Code_Medical_Necessity = 1, 'Medical_Necessity',

if(Denial_Code_NonCovered_Service = 1, 'Non_Covered_Service',

if(Denial_Code_Missing_Info = 1, 'Missing_Info',

if(Denial_Code_Prior_Authorization = 1, 'Prior_Authorization')))))

Measure:

Sum(aggr((Denial_Code_Appeals_Made), Accession_ID))

My requirement is for some Accession_IDs we have value for 2 or more denial codes conditions as '1'. So, when that is the case I need to sum the expression for all the conditions that has value '1'. The denial_code_experimental, denial_code_medical_necessity ....... are 5 different columns.

How can I change my condition to meet the requirement?

Please help.....

Thanks,

Rohini. M

Re: IF Condition

johnw — Fri, 21 Oct 2016 22:38:12 GMT

I'm not sure I follow. Can you post some sample data and what you want your chart to look like for that sample data?

But... maybe this?

sum(aggr(nodistinct Denial_Code_Appeals_Made, Accession_ID))

Re: IF Condition

Fri, 21 Oct 2016 23:44:01 GMT

Hi John,

Thanks for your reply.

Sample result table:

Type	Appeals_Made
	79282
Experimental	12016
Prior_Authorization	5643
Medical_Necessity	40263
Non_Covered_Service	14564
Missing_Info	6796

So here the appeals made is calculated based on the 'if' dimension condition. So, for some accession_IDs we have the denial_code values as '1' for 2 or more columns. so, I need to sum the value for all the columns that has value as '1'. But, based on the if condition I wrote if one accession_ID has denial_code_experimental as '1', then the accession_ID is not checked for other columns(Denial_code_Medical_Necessity,...). But I need to check all accession_ID's for all the conditions and I need to display the result as shown above. I know that if condition doesn't work for this requirement. pls let me know if we can do this in any other way in qlikview

Re: IF Condition

johnw — Fri, 21 Oct 2016 23:49:17 GMT

I'm guessing you need another table in your data model. Something like this:

Denial_Code, Denial_Code_Note
A,Experimental
A,Medical_Necessity
B,Prior_Authorization
C,Experimental
D,Prior_Authorization
D,Missing_Info
etc.

Then set your chart up like this:

Dimension = Denial_Code_Note
Expression = sum(Denial_Code_Appeals_Made)

Again, sample data and what you want your chart to look like would help nail it down. Like maybe it's an Accession_ID instead of Denial_Code in the table. I'm unclear how it all fits together.

Re: IF Condition

johnw — Mon, 24 Oct 2016 17:23:08 GMT

OK, that's the RESULT table. What does the DATA table look like?

Continuing to guess, I'll guess your data looks something like the top table here, and that for that data, you would want the results in the bottom right table.

If so, here's example script to build the additional table that makes it easy to build the result table.

Denial_Notes:

CROSSTABLE (Denial_Note, Denial_Flag)

LOAD

Accession_ID

,Denial_Code_Experimental as Experimental

,Denial_Code_Medical_Necessity as Medical_Necessity

,Denial_Code_Non_Covered_Service as Non_Covered_Service

,Denial_Code_Missing_Info as Missing_Info

,Denial_Code_Prior_Authorization as Prior_Authorization

RESIDENT Accessions

;

INNER JOIN (Denial_Notes)

LOAD 1 AS Denial_Flag

AUTOGENERATE 1

;

DROP FIELD Denial_Flag;

See attached.

Re: IF Condition

Tue, 25 Oct 2016 16:53:13 GMT

Thank You John. It Worked!