topic Re: difference between match and using an or statement? in QlikView

difference between match and using an or statement?

Thu, 28 Feb 2013 18:41:38 GMT

Hey guys. I have an issue that I need to solve quickly. Deadline is approaching.

What is the difference between using a "match" statement, or a bunch of "or" statements? I'm switching between the two and getting different results.

Here's my expression, once with "match" (the way I was using it) and once with "or" (again, the way I had used it):

count( if ( Match (DSFT_SEVY_DS, 'STD', 'DCSTD') and [Medicare Type]= 'D' and DSFTT_CD = 'B' and APEL_DS = 'Denial Overturned',DSFT_ID))
/count( if ( Match (DSFT_SEVY_DS, 'STD', 'DCSTD') and [Medicare Type]= 'D' and DSFTT_CD = 'B', DSFT_ID))

count( if DSFT_SEVY_DS = 'STD' or 'DCSTD' and [Medicare Type]= 'D' and DSFTT_CD = 'B' and APEL_DS = 'Denial Overturned',DSFT_ID))
/count( if DSFT_SEVY_DS = 'STD' or 'DCSTD' and [Medicare Type]= 'D' and DSFTT_CD = 'B', DSFT_ID))

I seem to be getting different results. I'm not sure which to use, or if I should be going a completely different route.

Please help!

Re: difference between match and using an or statement?

Gysbert_Wassenaar — Thu, 28 Feb 2013 18:46:57 GMT

Try this one:

count( if ((DSFT_SEVY_DS = 'STD' or DSFT_SEVY_DS = 'DCSTD') and [Medicare Type]= 'D' and DSFTT_CD = 'B' and APEL_DS = 'Denial Overturned',DSFT_ID))
/count( if ((DSFT_SEVY_DS = 'STD' or DSFT_SEVY_DS = 'DCSTD') and [Medicare Type]= 'D' and DSFTT_CD = 'B',DSFT_ID))

Better yet, get rid of the if's and use this:

count({<DSFT_SEVY_DS={'STD','DCSTD'},[Medicare Type]= {'D'},DSFTT_CD = {'B'}, APEL_DS = {'Denial Overturned'}>} DSFT_ID)

/count({<DSFT_SEVY_DS={'STD','DCSTD'},[Medicare Type]= {'D'},DSFTT_CD = {'B'}>} DSFT_ID)

Re: difference between match and using an or statement?

Thu, 28 Feb 2013 22:51:59 GMT

Wow. You just blew my mind, sir.

Re: difference between match and using an or statement?

MayilVahanan — Thu, 28 Feb 2013 22:54:23 GMT

Match and or give the same result.

For better performance, use set analysis.

count({<DSFT_SEVY_DS={'STD','DCSTD'},[Medicare Type]= {'D'},DSFTT_CD = {'B'}, APEL_DS = {'Denial Overturned'}>} DSFT_ID)

/count({<DSFT_SEVY_DS={'STD','DCSTD'},[Medicare Type]= {'D'},DSFTT_CD = {'B'}>} DSFT_ID)

Re: difference between match and using an or statement?

Thu, 28 Feb 2013 22:56:51 GMT

Would you mind giving me a quick explanation of your changes?

Re: difference between match and using an or statement?

Gysbert_Wassenaar — Fri, 01 Mar 2013 08:49:29 GMT

Sure. To begin with you had this:

count( if DSFT_SEVY_DS = 'STD' or 'DCSTD' ...

The if statement should look like: if( condition, A, B). So you were missing a parentheses for the if statement.

count( if(DSFT_SEVY_DS = 'STD' or 'DCSTD' ...

Next, the or wasn't quite correct. if( A=1 or 2, ....) should be if( A=1 or A=2,....)

count( if (DSFT_SEVY_DS = 'STD' or DSFT_SEVY_DS = 'DCSTD'....

On to the next issue. If you use both and and or in an if statement you should use parentheses to make sure the order of evaluation is what you want. A and B or C could mean (A and B) or C or it could mean A and (B or C). Those give different results.

count( if ((DSFT_SEVY_DS = 'STD' or DSFT_SEVY_DS = 'DCSTD') and [Medicare Type]= 'D'and DSFTT_CD = 'B' and APEL_DS = 'Denial Overturned',DSFT_ID))

And now the other expression with the weird curly brackets. That's a set analysis expression. If you use an if statement inside a function it's often possible to use a set analysis expression instead. What that does is first create a set of records matching the filters and then applies the function to that set. A set is calculated once per chart, while if statements are calculated per row. A set analysis expression will usually perform better because of that. See this document for a gentle introduction to set analysis expressions.

Re: difference between match and using an or statement?

Fri, 01 Mar 2013 12:19:40 GMT

You could not have been more helpful if your life depended on it. Thank you SO much!