The MINUS(-) Set Operator - What Is It Doing?

[shansen]

So, I have a rather unique use case, and I need to understand more deeply what is going on.  I need to be able to use set statements to do the logic A and B and (C or D) minus F.  I have a little experimental App here to troubleshoot behavior.  I have a statement that does something like A and B and (C or D):

{<TwoLetters = {'AD','DH'}>}{<Azip = {"*98*", "*65*"}>}{<Acolor = {"*e*"}> + <Azip = {"*79*"}>}

This results in two items.  If I want to remove one of those items to test out the minus F part, I tried:

{<TwoLetters = {'AD','DH'}>}{<Azip = {"*98*", "*65*"}>}{<Acolor = {"*e*"}> + <Azip = {"*79*"}>}{-<Acolor = {"*a*"}>}

When I do this, I don't get less rows, I get more.  What is the last {} doing?  How do I subtract something from the existing set?

[marksouzacosta]

Hi @shansen,

Nice app to test Set Analysis.

So, the way you are writing your Set Analysis is uncommon and unusual. They work but may make other developers a little insecure about it. We usually aggregate the things all together instead of splitting the parts with {}. I think you are trying to do some kind of auto-writing Set Expressions right?

Anyway, the {-<Acolor = {"*a*"}>} - awkwardly working as {<Acolor -= {"*a*"}>} - is overwriting your previous condition Acolor = {"*e*"} due the inheritance condition. Maybe you are looking for this:

{<TwoLetters = {'AD','DH'}>}{<Azip = {"*98*", "*65*"}>}{<Acolor = {"*e*"}-{"*a*"}> * <Azip = {"*79*"}>}

Or maybe keeping your structure but reverting the exclusion, so it removes the records from the beginning (but that will not eliminate the inheritance problem):

{<Acolor -= {"*a*"}>}{<TwoLetters = {'AD','DH'}>}{<Azip = {"*98*", "*65*"}>}{<Acolor = {"*e*"}> + <Azip = {"*79*"}>}

The same result is achieved by this:

{<TwoLetters = {'AD','DH'}, Azip = {"*79*"}, Acolor = {"*e*"}>}

I hope that helped.

Regards,

Mark Costa

[shansen · Accepted solution]

Mark,

Thanks for the reply.  It helps to have someone to bounce ideas off.  You guessed it, I'm doing some auto-writing of set expressions based on user input, and emulating some other filtering that is done in other code.  In this case it is much easier to build the set statement in pieces where the surrounding syntax is always the same, rather than accounting for other surrounding set statement elements that may or may not be there.

I agree that the {-<Acolor = {"*a*"}>} is over-writing things.  It is not only overwriting the previous condition Acolor = {*e*}, but all previous set statement elements. What I am looking for is a set that applies this first:
{<TwoLetters = {'AD','DH'}>}{<Azip = {"*98*", "*65*"}>}{<Acolor = {"*e*"}> + <Azip = {"*79*"}>}
which filters down to a set containing only 2 rows. Then I want to remove one of those rows based on a specific condition. I happened to pick {<Acolor -= {*a*}>}. The result should have only one row.

So, I must add that condition into one of the {} sections to have it operate how I am hoping. This works:
{<TwoLetters = {'AD','DH'}>}{<Azip = {"*98*", "*65*"}>}{<Acolor = {"*e*"}> + <Azip = {"*79*"}> - <Acolor={"*a*"}>}
The result is one row, with all the other conditions applied prior to the - <Acolor={"*a*"}> .

Interestingly, these do not work:
{<TwoLetters = {'AD','DH'}>}{<Azip = {"*98*", "*65*"}> - <Acolor={"*a*"}>}{<Acolor = {"*e*"}> + <Azip = {"*79*"}>}
{<TwoLetters = {'AD','DH'}> - <Acolor={"*a*"}>}{<Azip = {"*98*", "*65*"}>}{<Acolor = {"*e*"}> + <Azip = {"*79*"}>}

I have a theory regarding use of set operators (-, +, *, /) within multiple curly brace {} blocks. It looks like any time there is new set operator behavior in a {} block, then only the RH-most {} block gets used - All other {} blocks get ignored. New set operator behavior seems to be where a set operator:
1) Is used in the last {} block, when one was already used in the prior blocks, or
2) Was used previously, but is not used in the current {} block.

If a set operator is used in the last {} block, but has not been used in any prior blocks, then this is NOT new set operator behavior. All blocks will be combined with AND logic. That is why this works:

{<TwoLetters = {'AD','DH'}>}{<Azip = {"*98*", "*65*"}>}{<Acolor = {"*e*"}> + <Azip = {"*79*"}> - <Acolor={"*a*"}>}

The following examples seem to support the theory:

No set-operators in {} blocks:
{<TwoLetters = {'AD','DH'}>} results in only 7 rows.
{<Azip = {"*98*", "*65*"}>} results in 34 rows.
{<TwoLetters = {'AD','DH'}>}{<Azip = {"*98*", "*65*"}>} results in only 3 rows. AND logic is applied between the {} blocks.

Set operators used in 2nd and 3rd {} blocks. Only 3rd {} block is used:
{<TwoLetters = {'AD','DH'}>} results in only 7 rows.
{<Azip = {"*98*", "*65*"}> - <Acolor={"*a*"}>} results in 26 rows.
{<TwoLetters = {'AD','DH'}>}{<Azip = {"*98*", "*65*"}> - <Acolor={"*a*"}>} results in 1 row.
{<TwoLetters = {'AD','DH'}>}{<Azip = {"*98*", "*65*"}> - <Acolor={"*a*"}>}{<Acolor = {"*e*"}> + <Azip = {"*79*"}>} results in 75 rows. The same as {<Acolor = {"*e*"}> + <Azip = {"*79*"}>} alone. The context was replaced.

Set operator used in 1st {} block, but not in 2nd. Only the 2nd {} block is used.
{<TwoLetters = {'AD','DH'}>} results in only 7 rows.
{<TwoLetters = {'AD','DH'}> - <Acolor={"*a*"}>} results in 4 rows.
{<TwoLetters = {'AD','DH'}> - <Acolor={"*a*"}>}{<Azip = {"*98*", "*65*"}>} results in 34 rows, which is the same as {<Azip = {"*98*", "*65*"}>} alone. The context was replaced.

Maybe this is the intended behavior, or maybe it is a bug. I thought the context was only supposed to get replaced if a new set identifier was used (&, 1, etc.). At least I have a workaround. I can do my A and B and (C or D) minus F logic as long as the ANDs are all done in the first {} blocks, and the ORs and MINUSes are all done in the last {} block together.

I do think it would be good for someone from Qlik to look into this, and see if it is working as intended.

[shansen · Accepted solution]

Also, I found that this works: 

{<TwoLetters = {'AD','DH'}>*<Azip = {"*98*", "*65*"}>*(<Acolor = {"*e*"}> + <Azip = {"*79*"}> - <Acolor={"*a*"}>)}

So, the AND portion can be done with multiple <> elements with * in between, as long as you group your OR (+) and MINUS (-) portions with parentheses ().

[marksouzacosta]

I like this last one! It is more intuitive to understand.