Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

IF Condition

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

5 Replies
johnw
MVP
MVP

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))

Not applicable
Author

Hi John,

Thanks for your reply.

Sample result table:

 

TypeAppeals_Made
79282
Experimental12016
Prior_Authorization5643
Medical_Necessity40263
Non_Covered_Service14564
Missing_Info6796

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

johnw
MVP
MVP

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.

johnw
MVP
MVP

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.

Capture.PNG

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.

Not applicable
Author

Thank You John. It Worked!