Filter : Only show rows where $(vField1) <> $(vField2)

[#1]

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:

• Source Country
• Destination Country
• Payment Source Country
• Payment Destination Country

I want to allow the user to filter to see rows where, for example:

Source Country <> Destination Country

...or...

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

Any ideas? Am I making sense?

[effinty2112]

Hi Nathaniel,

Hope this qvw gives some ideas. It works as it stands but it's not terribly scalable if you want to consider more fields. A reload will generate a 1000 record table with random countries.

[swuehl]

Using Andrew's sample data, attached are two other approaches to filter your records, using two variables to select the field names to compare and a Sum(IF()) resp. Set Analysis expression.

[#4]

This is great.

Few questions/comments.

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 simplify the expression you used? Currently he expression is quite long. That's fine, but more concerning, the expression must be updated if I add another country field.

=Aggr(

if(

//Check Source Country against others

SubStringCount(

if(WildMatch('|' & Concat($Field,'|'),'*|Destination Country*'),[Destination Country] & '|' ) &

if(WildMatch('|' & Concat($Field,'|'),'*|Payment Source Country*'),[Payment Source Country] & '|' ) &

if(WildMatch('|' & Concat($Field,'|'),'*|Payment Destination Country*'),[Payment Destination Country] & '|' ),

[Source Country]) * WildMatch('|' & Concat($Field,'|'),'*|Source Country*')

+

//Check Destination Country against others

SubStringCount(

if(WildMatch('|' & Concat($Field,'|'),'*|Source Country*'),[Source Country] & '|' ) &

if(WildMatch('|' & Concat($Field,'|'),'*|Payment Source Country*'),[Payment Source Country] & '|' ) &

if(WildMatch('|' & Concat($Field,'|'),'*|Payment Destination Country*'),[Payment Destination Country] & '|' ),

[Destination Country]) * WildMatch('|' & Concat($Field,'|'),'*|Destination Country*')

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?

[#5]

This is great, few questions:

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:

1. a way of setting vField1,
2. a way of setting vField2,
3. 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?)

Makes sense?

[swuehl · Accepted solution]

You can create a Select - Select in field action to select the IDs as required (with an advanced search as used in the set expression of the second table expression.

Field: ID

Search string: ='=[$(vField1)] = [$(vField2)]'

And instead of the button, you can use variable event triggers (Settings - Document properties - Triggers) to call the action, but I wouldn't recommend that automatism.

edit:

Attached also a version with variable event triggers

[#7]

So this requires an ID field, and an "advanced search expression" on that ID field. Thanks!