Filter : Only show rows where $(vField1) <> $(vField2)
In other words, only show rows where one column's value is not equal to another...
My data has many geographic columns.
For example, let's say it has 4 geographic columns:
Payment Source Country
Payment Destination Country
I want to allow the user to filter to see rows where, for example:
Source Country <> Destination Country
Source Country <> Payment Source Country
In my case, the user could compare any field (of 4 fields) to any other field (3 other choices)
4x3 = 12
That's 12 different comparisons (please assume these are all meaningful)
I can set vField1 and vField2, so the user can choose vField1 = Source Country , and vField2 = Destination Country
But how can I filter the rows to show only rows where $(vField1) <> $(vField2)
1) I have already tried doing a Set Expression, to get me the comma-separated list of IDs where $(vField1) <> $(vField2)
But in my case, there are many millions of rows, and I think Qlikview can't concatenate that many IDs into a single filter-string.
2) I also don't want to add 12 new columns to the load script -- I don't want to add any new columns to the load script. It feels too manual.
3) And lastly, I've tried adding a listbox whose value is the expression $(vField1) <> $(vField2) , but it doesn't work as you might expect. Choosing "0" or "-1" doesn't isolate the specific rows where the condition is true, it separately filters the values in $(vField1) where the condition is true, and filters the values in $(vField2) where the condition is true. Row-wise the condition could still be true or false.
4 )I also agree with this statement: "Just because they can choose 12 different comparisons, doesn't mean they should be allowed to. Only allow those comparisons that are meaningful. Maybe only 3 comparisons are meaningful -- so only 3 extra columns to the load script" But you should assume this is not an option
I like your use of $Field, but assume (if it makes things easier), that you have access to vField1 and vField2, and the user always has picked two (different) fields stored in those values.
3) Can we automate the actual "filtering" part? Currently, (I think) the user must manually select from the filter of the "Similar country?" column, whether they want "Distinct" or "Common". (In doing this manually, a filter is explicitly applied to the [ID] field). Is there any way to apply this filter programatically/automatically, using a trigger and an action?
1) Does this require a (unique-for-each-row) ID field? That's easy, I can add RowNo() or IterNo() [as you did] if needed.
2) Is there a way to actually apply a real filter? In this case, the tables filter, but it's because the Expression value is "0" in one case and "<>0" in the other. So it's there is no "selection" applied; if I check Ctrl+Q [Current Selections] -- there is no "selection"; no "real filter".
I'll wait for you guys to answer my questions. Both are great answers, but ideally, the parts I need are:
a way of setting vField1,
a way of setting vField2,
and on changing either of them [automatically] Current Selection Clears All, and then applies a filter so that only the rows where vField1 <> vField2 are shown in any chart on the entire dashboard, not just the charts with this special expression you have.
You (swuehl) have part 1, and 2. Andrew Walker does not. But that's an easy change.
Andrew Walker requires one manual step (if I understand -- must manually filter the column) to achieve step 3.
And I'm not sure whether (swuehl) your solution can achieve step 3 -- you achieve it only (implicitly) in the tables you've designed, without applying any (explicit) filter (i.e. couldn't bookmark anything meaningful?)