18 Replies Latest reply: May 17, 2012 2:44 AM by Jason Michaelides RSS

How to un-select values in a table

thomasboell

Hi all,

 

I hope someone out there has a sparkling idea as I am slowly going nuts...

 

I am trying to solve in issue in the area of cash matching:

I have a table that contains open transactions as well as unprocessed payments.  Each record set has a unique transaction number (besides a wide variety of other information).

 

The user needs to "match" open transactions to a particular open payment. When I say match, I mean that I want the user to create a kind of matching sheet that displays a number of transactions and an open cash item where the total amount equals "0" (that is the amount of transactions equals the amount of the cash item).

 

(I know this is not exactly what Qlikview was intended to do, but in this particular situation would by far be the best solution still)

 

I show all open items in a pivot table.

 

Now, what I want the user to do is to click on a transaction number to UN-SELECT the chosen transaction. This is repeated with other transaction numbers until only those transactions are left that match with an open payment in the list.

 

So , instead of the regular Qlikview behaviour (that is to set a filter on the particular transaction number I have clicked on) I need it to do the opposite (to un-select the chosen transaction number and show all transaction number except the one I have clicked on).

 

I know there is an inverse selection option in the menu, but as far as I can work it out, this will a) reverse all fitlers set and not only the one on transaction numbers, and b) cannot be used when the user needs to un-select multiple values of transaction number.

 

Also set analysis does not seem to be the answer, as after I have chosen one transaction number, I cannot proceed to choose a second or third one.

 

Does anybody have an idea how to solve this?

 

Many thanks in adance!!!

 

Thomas

  • How to un-select values in a table
    Jason Michaelides

    I don't quite follow what you want but I think you are essentially saying you want a listbox of transaction numbers and the user should be able to select a bunch of them in the usual way.  Your chart should show a bunch of expressions for those Tx numbers that are NOT in the list of selected Tx numbers - correct?

     

    If so, say one of your expressions was Sum(Sales) you could try

     

    Sum({<TxNumber=E()>} Sales)

     

    Hope this helps,

     

    Jason

    • How to un-select values in a table
      thomasboell

      Thanks Jason,

       

      I am actually trying to avoid the listbox as the number of transaction numbers is quite high.

       

      Ideally the user should be able to choose those transactions types in the pivot table itsself - transaction number is one of the fields displayed in the pivot table. The user coudl then scroll through the pivot table and un-select any transactions that cleary do not match to the payment.

       

      Is that possible at all?

       

      Cheers,

      Thomas

      • How to un-select values in a table
        Jason Michaelides

        I'm pretty sure you can't multi-select items in a pivot table (i.e. ctrl+click doesn't work), so no it's not possible. 

         

        Any reason your users must select the items that don't match manually?  You could have a button that automatically removes all these for you.  Just one click then...

        • How to un-select values in a table
          thomasboell

          Hmm,

           

          unfortunately, the users will have to identify the transactions themselves, there is no logic I could use to automatically identify or remove unwanted transactions.

           

          They could off course identify only those that actually fit to the payment. But then again I have the same issue - clicking on a value in Qlikview will filter that exact value...

          • How to un-select values in a table
            thomasboell

            Hi all,

             

            I have managed to solve the first step: When I create a trigger action on selection of the field transaction number, and the action is to revert the selection, I get what I was looking for: Clicking on a value in a pivot table actually excludes the value I clicked on.

             

            (Please note: I am using QV in German, so my translations of menu items might not be accurate)

             

            Now I am struggling with the second step: The user should be able to choose a second/third transaction number to exclude those as well.

             

            Right now, when I choose another transaction number, QV does exclude the new one and brings back the number previously excluded.

             

            I want to exclude all the values the user has clicked on...

            • How to un-select values in a table
              Jason Michaelides

              Maybe use a variable and change it with another trigger?  You will need a data island of TxNumbers (TxNumber_DI)

               

              Set the variable vExcludedIDs = '' to start with.

               

              Provide a listbox with the TxNumber_DI field in it.  Then, each time the TxNumber_DI changes (i.e. each time a user clicks on a number) set the variable to be

               

              =vExcludedIDs & '|' & TxNumber_DI

               

              This will build up a list of pipe-separated TxNumbers and in your Select In Field action you can add TxNumber to the field box and ='(' & vExcludedIDs & ')' in the expression box.

               

              Not sure if this will achieve what you want but it's another path to consider...

               

              Jason

              • How to un-select values in a table
                thomasboell

                Thanks Jason,

                 

                I am almost there!

                 

                I can see how the variable is building up the pipe-separated TxNumbers.

                 

                The only thing I cannot get to work is to exclude the TxNumber stored in the variable in my pivot table. I have setup an action and think I have followed what you have said, but it does not change the way the pivot table displays my values.

                 

                I have attached a screen shot - is that what you ment?

                 

                Bildschirmfoto 2012-05-16 um 14.16.33.png

                • How to un-select values in a table
                  Jason Michaelides

                  I think my suggestion is slightly off actually.  Try setting the variable to be:

                   

                  ='(' & Concat(DISTINCT TxNumber_DI,'|') & ')'  NOTE: the "=" sign must be in your variable expression.

                   

                  And get rid of the triger to change it.  Now change the expression in the button action to ="<> $(vExcludedIDs)"

                   

                  See if that works...

                   

                  By the way - you may need to add a preceding action in the button to clear the current selections for TxNumber

                  • How to un-select values in a table
                    thomasboell

                    Hi Jason,

                     

                    that does not work: The variable does not store previously selected TxNumbers anymore and rather only stores the last selection...

                     

                    Any idea?

                     

                    (Thanks again for all the effort you put into this!!!!)

                    • How to un-select values in a table
                      Jason Michaelides

                      Oops!  Forgot about the incremental selections...that'll teach me for breaking for lunch!

                       

                      Go back to the previous solution with the trigger.  Set the trigger action to change vExcludedIDs to:

                       

                      =IF(vExcludedIDs='',TxNumber_DI,vExcludedIDs & '|' & TxNumber_DI)

                       

                      and put the button action back to ='(' & vExcludedIDs & ')'

                      Now, add another action to the same button of type Select Excluded and field TxNumbers.

                       

                      Let me know...!

                      • How to un-select values in a table
                        thomasboell

                        Even closer!!!

                         

                        It actually does what I need now, but...:

                         

                        After I have executed the actions on my "Exclude TxNumbers"-Button, the Variable seems to loose its values - it shows "-" in my textfield afterwards!?!

                         

                        I have no idea why its doing this, but it seems to be related to the "Select excluded" command - the same happens when I use the right click menu and choose select excluded...

                         

                        I should mention that I had to put the expression on the "on selection" action of my TxNumber ID field - "on update" would not work.

                         

                        Also I have slighty changed the expression to:

                         

                        =If(%Key_OPs>0,(IF(vExcludedIDs='',%Key_OPs,vExcludedIDs & '|' & %Key_OPs)))

                         

                        as this avoids getting any empty values between two pipes (what would happen i.e. if I un-select a value by clicking on it a second time.

                         

                        If I can only save the values in my variable, I am there!?!

                         

                        Maybe you have another of your sparkling ideas???

            • How to un-select values in a table
              Emma Self

              Hi Thomas,

               

              If you have a current selections box (I highly recommend this on every sheet in every application you build) you can select multiple values and then right mouse button over the selection in the current selections box and chose to select excluded.  If, for example you've chosen Bath and London from town, then the selections box would change from "Bath, London" to "NOT Bath, London".

               

              Is this what you're after?

               

              Cheers,

              Emma

            • Re: How to un-select values in a table
              Emma Self

              Hi Thomas,

               

              If you have a current selections box (I highly recommend this on every sheet in every application you build) you can select multiple values and then right mouse button over the selection in the current selections box and chose to select excluded.  If, for example you've chosen Bath and London from town, then the selections box would change from "Bath, London" to "NOT Bath, London".

               

              Is this what you're after?

               

              Cheers,

              Emma