Why does this set analysis work?

[#1]

I am trying to gain a better understanding of set analyis. I've read the QlikLearn set analysis presentation and I'm starting to understand how to write basic expressions on my own but still need some help filling in the gaps.

I saw this example in another post, and it works, but I'm not sure why it works.  I'm hoping someone can help explain it to me.

Sum({< Column_A = {">=5<=12"}, Column_A = {"=Column_A <= Column_B"} >} [MyMeasure])

In the first part of the modifier, I know that all records where column A is greater than or equal to 5 and less than or equal to 12 are being pulled, but why the quotation marks?  How does it know that the >=5<=12 is an expression that needs to be evaluated and not a string?  Is it because it uses double quotes instead of single quotes?

In the second part of the modifier, I know it is checking to see if Column_A is less than or equal to Column_B, but why does it work?  Why does it have two equal signs, and why is Column_A included a second time in the quotations?  Why isn't it just {"<=COLUMN_B"}? 

Thank You!

[swuehl · Accepted solution]

First, I believe using two field selections for the same field in one set modifier is not a good idea, I think one of them just doesn't get considered.

Second, you are right that the double quotation marks make the difference: They allow to state an expression, a search that filters the values in your field selection (just like you can write >5<=12 in the search bar of a list box).

The search will look over all distinct values of Column_A and filter only those that match the search string.

If you would use this as search string

<= Column_B

QV probably can't tell what the value of Column_B should be, right?

So you need to do a table record based comparison, where Column_A and Column_B reside in one table and you can go through the table and compare the values of Column_A and Column_B per record.

This might work, but you will get into trouble where there are records for the same value of Column_A where the condition is one time TRUE and one time FALSE. Should QV consider this value of Column_A in the set selection or not?

So to get around this issue when doing a record based comparison, you normally use a field with a uniqe value per record, like a key field:

KEY = {"=Column_A <= Column_B"}

i.e. select all KEY values where COLUMN_A is equal or smaller than COLUMN_B in the same table row.

[Note that doing records based comparisons in a set expression will not perform as good as distinct values / symbol based filtering in most cases]

Hope this helps,

Stefan