topic Re: Combining Set Analysis Operators in QlikView

Combining Set Analysis Operators

mjamesking — Wed, 13 Apr 2016 22:13:35 GMT

Hi,

I know that to create a union between two sets you use syntax such as

and to create the intersection of twos sets you use

But is there any was of combining these operators?

For instance I would like to create the intersection of sets, each of which is the union of two sets itself

e.g.

(<set1> + <set2>) * (<set3> + <set4>)

Doesn't like the parenthesis though

Any ideas?

Matt

Re: Combining Set Analysis Operators

swuehl — Wed, 13 Apr 2016 22:45:22 GMT

Seems to work for me.

Could you upload a sample that doesn't work as expected (and maybe add the expected result, too)?

edit:

Tested on QV12 IR with set expressions like

=Concat({(<TASK = {2}>+<TASK = {1}>)*(<TASK = {1}>+<TASK = {3}>)}TASK,', ')

=Concat({<TASK = {2}>+(<TASK = {1}>)*(<TASK = {1}>)+<TASK = {3}>} TASK,', ')

Re: Combining Set Analysis Operators

maksim_senin — Wed, 13 Apr 2016 22:55:47 GMT

Hi,

Not sure what your problem is but use of parenthesis is covered by manual:

sum( {$<Year = Year + ({“20*”,1997} – {2000}) >} Sales )

sum( {$<Year = (Year + {“20*”,1997}) – {2000} >} Sales )

These two expressions have different logic due to use of parenthesis.

Best regards,

Maxim

Re: Combining Set Analysis Operators

sunny_talwar — Wed, 13 Apr 2016 23:02:14 GMT

What exactly are your sets, may be the two sets don't have anything in common and that's why you don't see any results?

Re: Combining Set Analysis Operators

mjamesking — Wed, 13 Apr 2016 23:16:32 GMT

Thanks for the responses everyone. How do I upload a qlikview file to the forum? I've put together a simplified example

Re: Combining Set Analysis Operators

sunny_talwar — Thu, 14 Apr 2016 09:05:28 GMT

Check here: Uploading a Sample

Re: Combining Set Analysis Operators

mjamesking — Thu, 14 Apr 2016 14:40:35 GMT

Here we go...

Here's a simplified example of what I'm trying to do. The first two combined sets (Union) work as expected, then what I want to do is to get the intersection of these two combined sets, but the result isn't as expected, I would expect the and (Count) to be 3 (ids 4, 8 and 10).

Cheers

Matt

Re: Combining Set Analysis Operators

sunny_talwar — Thu, 14 Apr 2016 15:02:58 GMT

This seems to work:

=COUNT({(<Category1 = {A}> + <Category2 = {Red}>) * (<Category3 = {Bananas}> + <Category4 = {Brunette}>)} ID)

Re: Combining Set Analysis Operators

Peter_Cammaert — Thu, 14 Apr 2016 15:28:35 GMT

Neverming the red wiggly lines underneath set expression parts in aggregation expressions, if that is what is bothering you. In many cases, even the syntax checker in the expression editor isn't aware of what QlikView is capable of.

Re: Combining Set Analysis Operators

swuehl — Thu, 14 Apr 2016 16:59:01 GMT

I don't even get red wiggly lines.

Matt, I believe you should get your expected result if you use parentheses as you indicated in your OP (but it seems you've removed these from your sample file).

=COUNT({(<Category1 = {A}> + <Category2 = {Red}>) * (<Category3 = {Bananas}> + <Category4 = {Brunette}>)} ID)

You will get an error if you place the set identifier at the wrong place:

Error:

=COUNT({$(<Category1 = {A}> + <Category2 = {Red}>) * (<Category3 = {Bananas}> + <Category4 = {Brunette}>)} ID)

While this should be ok (or just remove the default set identifier:

=COUNT({($<Category1 = {A}> + $<Category2 = {Red}>) * ($<Category3 = {Bananas}> + $<Category4 = {Brunette}>)} ID)

Re: Combining Set Analysis Operators

mjamesking — Thu, 14 Apr 2016 18:00:12 GMT

Thanks everyone for your help, I was close, but the combination of the $ and the ( was making qllikview think I was referencing a variable so I didn't pursue this route, both sunindia and swuehl approaches work perfectly