topic Multiple selection criteria in a set analysis? in QlikView

Multiple selection criteria in a set analysis?

graham-crooks — Tue, 31 Jan 2012 08:46:01 GMT

Hi All

I have an expression that covers some multiple selection criteria / options between the inter-related values of two fields, d01 and d02. As an example:

sum(

if(AdmitMethod<>'81'

and ((d01 like 'I21*') or (d01 like 'I22*') or ((d01 like 'R07*') and ((d02 like 'I21*') or (d02 like 'I22*'))))

,AdmCounter))

So, this works of course, but it's a little wieldly and will only get worse the more combinations of d01 and d02 I have to cover.

Using a set analysis approach, so far I've arrived at:

sum({<~AdmitMethod={'81'},d01Ch={'I21','I22','R07'}>}AdmCounter)

where d01Ch is, usefully in this case, the 3 character version of d01 (which can be 4-6 characters long, the first 3 providing the grouping).

As you can see the two expressions are different. I'm wondering if there's a neat and tidy way to incorporate the d01/d02 relationship for ... OR (d01 = R07 AND (d02=I21 OR d02=I22)) into the set analysis, whilst maintaining the 'd01Ch can equal I21 or I22' part.

Can anyone suggest a way to achieve this please?

All help and advice most welcome.

Multiple selection criteria in a set analysis?

perumal_41 — Tue, 31 Jan 2012 08:57:29 GMT

Try this below expression

Sum({<AdmitMethod-={'81'},d01= {'121*','122*','R07*'},d02= {'122*'}>}AdmCounter)

Regards ,

Perumal A

Multiple selection criteria in a set analysis?

CELAMBARASAN — Tue, 31 Jan 2012 09:13:14 GMT

Hi,

Try this

sum({<AdmitMethod-={'81'},d01={'I21*','I22*','R07*'},d02={'121*','122*'}>} AdmCounter)

Celambarasan

Multiple selection criteria in a set analysis?

graham-crooks — Tue, 31 Jan 2012 22:45:50 GMT

Hi Perumal & Celambarasan - Thank you for your suggestions.

As the various elements within the set analysis effectively form an AND statement, sadly this doesn't work; the determination effectively becomes 'if d01 = any of... AND if d02 = any of...'. The problem I have here is that if d01 is I21* or I22* then d02 certainly will not be; if d01 is R07, then d02 might be I21* or I22*. But being willing to try anything once, I've tried the suggestion. The data simply disappears as there is now no match. Any other suggestions would, however, be most welcome.

Re: Multiple selection criteria in a set analysis?

Miguel_Angel_Baeyens — Tue, 31 Jan 2012 23:38:32 GMT

Hi Graham,

What about using two (or more) set modifiers instead of just one?

Sum({< AdmitMethod -= {'81'}, d01 = {"121*", "122*", "R07*"} > + < AdmitMethod -= {'81'},  d02 = {"122*"} >} AdmCounter)

That "+" sign means that the values of AdmCounter will be summed up when values correspond to either the first or the second modifier (things between <>). The above should return the same as

Sum({< AdmitMethod -= {'81'} > * (< d01 = {"121*", "122*", "R07*"} > + < d02 = {"122*"} >)} AdmCounter)

That can be read as "return the sum of values in AdmCounter for the data set where AdmitMethod is not 81 and ("*") either d01 or d02 (or both) ("+") have the values specified".

Hope that helps and makes sense.

Miguel

Re: Multiple selection criteria in a set analysis?

graham-crooks — Wed, 01 Feb 2012 00:34:24 GMT

Hi Miguel

Thank you for this inspiration - I knew there must be a way! Using your thinking I arrived at:

sum({<AdmitMethod-={'81'}>*(<d01={'I21*','I22*'}> + <d01={'R07'},d02={'I21*','I22*'}>)}AdmCounter)

which now matches the logic of my original if statement and the two charts provide the same answers.

I hadn't appreciated that I could use more than one set modifier grouping.

This is really helpful. Thank you.