topic Set the value of a variable when selecting a value in a Listbox in QlikView

Set the value of a variable when selecting a value in a Listbox

salto — Fri, 01 Oct 2010 14:42:19 GMT

Hello,

I'd like to set the value of the Currency named variable to €, when the value in a ListBox changes to 'Consolidation'. Is this possible to perform with a Macro? I have tried with Sheet Object Event Trigger "Set Variable", but it changes the variable to € whatever value I select in the List Box.

Any idea will be appreciated.

Set the value of a variable when selecting a value in a Listbox

jonathandienst — Fri, 01 Oct 2010 15:00:04 GMT

Rather than using a macro or trigger, I suggest that you use an expression in the variable. Something like:

=If(only(field1) = 'Consolidation,'€', '$')

Change field1 to match your model.

This should work as long as only one value for field1 is selected. If multiple values are allowed, you could use GetFieldSelections instead.

Jonathan

Set the value of a variable when selecting a value in a Listbox

salto — Tue, 05 Oct 2010 10:30:10 GMT

Thanks Jonathan, sorry for the delay. Where should I use the expression for the variable?

I have tried by writing the expression you provided in the variable definition for the document (Document Properties -> Variables). But no luck, the behaviour is not the expected one, because the Currency variable does not change when I change the selection in the ListBox (from Consolidation to No Consolidation and viceversa).

I'll keep on trying...

Set the value of a variable when selecting a value in a Listbox

jonathandienst — Tue, 05 Oct 2010 10:53:56 GMT

I suggest that you first test the expression in a text box. Assuming that the expression variable has a leading '=' sign, as per my post, then put =vCurrencySymbol into the text box Then check whether the expression is working correctly.

(If you define the variable without a leading '=', then you can define the text box =$(vCurrencySymbol))

Once the expression works correctly, you can use it wherever you need the currency symbol.

Regards

Jonathan

PS - if you cannot get the expression to work, I suggest that you post your model so that we can look at this more closely.

Set the value of a variable when selecting a value in a Listbox

salto — Tue, 05 Oct 2010 11:29:36 GMT

Hello Jonathan,

the expression works perfectly in the textbox: it shows $ or €. But the variable in the inputbox does not change. Should I set up the variable in the load script?

Thanks.

Set the value of a variable when selecting a value in a Listbox

jonathandienst — Tue, 05 Oct 2010 13:04:31 GMT

I am a little confused. The expression calculates a value when you change the selection in a list box. Where does the input box come into this?

Jonathan

Set the value of a variable when selecting a value in a Listbox

salto — Tue, 05 Oct 2010 14:33:29 GMT

Sorry, it has been my fault: the variable is used in an input box to select the currency in which the sales amounts have to be shown.

The goal is to make the currency value to be € (and never $) when the "Consolidation" value is selected in the listbox.

I apologize for not explaining myself clearly.

Set the value of a variable when selecting a value in a Listbox

jonathandienst — Tue, 05 Oct 2010 14:57:32 GMT

OK that explains it.

Yes, you were right about needinga trigger - you will need to an OnSelect field event trigger. Use a Set Variable action and put the variable that should contain the currency symbol in the Variable box and enter the expression =If(only(field1) = 'Consolidation,'€', '$') in the Value box.

(Obviously with the correct field name in place of field1).

Remember that this expression only works if only one value is selected in field1.

Jonathan

Set the value of a variable when selecting a value in a Listbox

salto — Tue, 05 Oct 2010 17:35:46 GMT

That did the trick. Many thanks!