6 Replies Latest reply: Jan 4, 2016 10:55 PM by Nathaniel Anderson RSS

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

    Nathaniel Anderson

      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?

        • Re: Filter : Only show rows where $(vField1) <> $(vField2)
          Andrew Walker

          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.

            • Re: Filter : Only show rows where $(vField1) <> $(vField2)
              Nathaniel Anderson

              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?

            • Re: Filter : Only show rows where $(vField1) <> $(vField2)
              Stefan Wühl

              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.

                • Re: Filter : Only show rows where $(vField1) <> $(vField2)
                  Nathaniel Anderson

                  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?