Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How to un-select values in a table

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

1 Solution

Accepted Solutions
jason_michaelid
Honored Contributor II

How to un-select values in a table

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...!

18 Replies
jason_michaelid
Honored Contributor II

How to un-select values in a table

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

Not applicable

How to un-select values in a table

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

jason_michaelid
Honored Contributor II

How to un-select values in a table

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...

Not applicable

How to un-select values in a table

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...

Not applicable

How to un-select values in a table

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...

jason_michaelid
Honored Contributor II

How to un-select values in a table

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

Not applicable

How to un-select values in a table

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

Not applicable

Re: How to un-select values in a table

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

Not applicable

How to un-select values in a table

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

Community Browser