topic Re: IF Statement (error) in QlikView

IF Statement (error)

Mon, 19 Nov 2012 15:13:04 GMT

Hello,

I am thinking this is an easy fix-

Count Concatenate

IF(AMOUNT>0, Count(DISTINCT(DOS&'-'&PAT_ENC_CSN_ID&'-'& [Proc Code]&'-'&[Prov ID])), 0)

I can see the individual counts working, but the total shows 0. Any idea why this is happening?

~EK

Re: IF Statement (error)

Gysbert_Wassenaar — Mon, 19 Nov 2012 15:20:37 GMT

You probably have to aggregate over your dimensions.

aggr(IF(AMOUNT>0, Count(DISTINCT(DOS&'-'&PAT_ENC_CSN_ID&'-'& [Proc Code]&'-'&[Prov ID])), 0), dim1,dim2)

If you use a straight table you could set the Total Mode of the expression to Sum of Rows.

Re: IF Statement (error)

Mon, 19 Nov 2012 15:32:18 GMT

As easy as switching the total mode. Thanks for the extra set of eyes!

-EK

Re: IF Statement (error)

Mon, 19 Nov 2012 16:11:46 GMT

Hi Gysbert,

Wondering if you could help with making this IF statement a little more complicated. I have 2 procedure codes that would skew my data. 99291-is a code listed for 30-74 min of a hospital visit. Once the visit exceeds that time-99292 would be used to show that an additional 30 min was needed for the visit and so on. My current concatenation would count that twice as they are different [Proc Code]s. Do you know what to add so that proc codes 99291 and 99292 are only counted once if listed on the same day?

-EK

Re: IF Statement (error)

Gysbert_Wassenaar — Mon, 19 Nov 2012 18:31:38 GMT

Try this:

Count({<AMOUNT={'>0'},[Proc code] -={'9929*'}>+<AMOUNT={'>0'},[Proc code]={'99291'}>} DISTINCT(DOS&'-'&PAT_ENC_CSN_ID&'-'& [Proc Code]&'-'&[Prov ID]))

It should count all records where AMOUNT > 0 and Proc code doesn't start with 9929, unless it's 99291.

Re: IF Statement (error)

Mon, 19 Nov 2012 20:45:18 GMT

Hmmmm still doesnt seem to be recognizing the formula. When i paste this formula in- {'9929*'}>+<AMOUNT={'>0'},[Proc code]={'99291'}>} DISTINCT(DOS&'-'&PAT_ENC_CSN_ID&'-'& [Proc Code]&'-'&[Prov ID])) is highlighted in red. I can imagine this is a little difficult when you are not working with real fields. I don't own this app either so not sure how I could send you a template version.

If i get rid of the negative sign before the equal sign -={"9929*} it likes the formula, although ithat changes the formula to include vs exclude.