topic Forcing selection in listbox according to item selection in other listbox with macro in QlikView

Forcing selection in listbox according to item selection in other listbox with macro

louwriet — Tue, 13 Sep 2011 16:05:30 GMT

Hi,

I would really appreciate some help with something im stuck with for a while now.

I came across this thread that has exactly what I want and implemented the macro into my model.

http://community.qlik.com/message/43182#43182 this is where you force a selection in a listbox.

I have two listboxes with codes in that are the same but form two different tables in my model. When you do a selection on one or more items in the one listbox it will cause the items with the same value in the other listbox to be selected.

I have put in a messagebox in the macro to see if it picks up the selected value and it does – but for some reason it does not select the items in the other listbox.

Macro that I activate with a button as in sample app in link above

SUB GetSelectedValues

SET fx = ActiveDocument.Fields("Code")

SET fy = ActiveDocument.Fields("TransCodeKey")

SET fyV = fy.GetNoValues

FOR i = 0 TO fxV.Count -1

fyV.Add

fyV(i).Text = fxV(i).Text

msgbox(fyV(i).Text)

fyV(i).IsNumeric = False

fy.SelectValues fyV

END SUB

It works 100% in demo app but am I missing perhaps a setting somewhere in qlikview ? Can it be settings ? the 2 tables of mine are excatly the same - so it cant be that the values are not equal.

Thank you

Louw

Forcing selection in listbox according to item selection in other listbox with macro

swuehl — Tue, 13 Sep 2011 16:16:54 GMT

Hi Louw,

if that's exactly your code:

SUB GetSelectedValues

SET fx = ActiveDocument.Fields("Code")

SET fy = ActiveDocument.Fields("TransCodeKey")

SET fyV = fy.GetNoValues

FOR i = 0 TO fxV.Count -1

fyV.Add

fyV(i).Text = fxV(i).Text

msgbox(fyV(i).Text)

fyV(i).IsNumeric = False

fy.SelectValues fyV

END SUB

I think you should recheck your variables, e.g. in

FOR i = 0 TO fxV.Count -1

fxV does not exist, does it?

Not sure which field you want to copy into the other, so I can't correct it, but I think you should take a look at the variables.

Regards,

Stefan

Re: Forcing selection in listbox according to item selection in other listbox with macro

jonathandienst — Tue, 13 Sep 2011 19:29:58 GMT

Hi Louw

I suggest that you add the line below...

SUB GetSelectedValues

     SET fx = ActiveDocument.Fields("Code")

     ' *** I think you need this line ***
     SET fxV = fx.GetSelectedValues

     SET fy = ActiveDocument.Fields("TransCodeKey")
     SET fyV = fy.GetNoValues

     FOR i = 0 TO fxV.Count -1
           fyV.Add
           fyV(i).Text = fxV(i).Text
            msgbox(fyV(i).Text)
           fyV(i).IsNumeric = False
     NEXT
     fy.SelectValues fyV

END SUB

Hope that helps

Jonathan

Forcing selection in listbox according to item selection in other listbox with macro

rwunderlich — Wed, 14 Sep 2011 00:10:15 GMT

Just checking -- are you using macro instead of action because you are on version 8? This is easier with actions.

-Rob

Forcing selection in listbox according to item selection in other listbox with macro

louwriet — Wed, 14 Sep 2011 04:27:58 GMT

Hi Jonathan for some reason i missed that line in my copy in past, so the line was in my macro.

SUB GetSelectedValues

     SET fx = ActiveDocument.Fields("Code")
     SET fxV = fx.GetSelectedValues


     SET fy = ActiveDocument.Fields("TransCodeKey")
     SET fyV = fy.GetNoValues

     FOR i = 0 TO fxV.Count -1
           fyV.Add

            msgbox(fxV(i).Text) ‘’ this picks up correct value

           fyV(i).Text = fxV(i).Text

            msgbox(fyV(i).Text)    ‘’ this picks up correct value
           fyV(i).IsNumeric = true
     NEXT
     fy.SelectValues fyV

END SUB

it picks up the correct values in msgboxes but doesn't select the values ? dont understand it.

thanks for help

Louw

Forcing selection in listbox according to item selection in other listbox with macro

louwriet — Wed, 14 Sep 2011 04:36:55 GMT

Hi Rob,

I am on version 10, will this be easier to accomplish with actions ? I Would appreciate if you can push me in the right direction with actions.

It would be great if I can solve this problem that I have.

Regards

Louw

Forcing selection in listbox according to item selection in other listbox with macro

louwriet — Wed, 14 Sep 2011 04:46:02 GMT

Hi Stefan,

As you will see in my reply to Jonathan, i did put an extra msgbox to show both values in variables and it does pick up the right values - but it doesnt select that values in the other listbox, i have swap around the fields aswell just to test and it does not work. I am on version 10 if this matters.

Thank you for your help.

Regards

Louw

Re: Forcing selection in listbox according to item selection in other listbox with macro

swuehl — Wed, 14 Sep 2011 08:57:43 GMT

Hi,

I think there are at least three possibilities:

1) fix your code (well, that's where we are stucked, right?)

2) you could use a macro code I think Rob Wunderlich has suggested some time ago on a similar request, something along this:

ActiveDocument.Fields("date2").SelectValues ActiveDocument.Fields("date1").GetSelectedValues

3) I would prefer an action over an macro, try replacing your External - run macro action with a Selection - select in field action,

then use

TransCodeKey

as Field (without any quotes or equal sign, just the plain field name)

and something like

='=sum({<Y=X >} 1)'

as search string.

Regards,

Stefan

edit: Just for clarification, you need to replace X,Y with your fields TransCodeKey and Code, like

='=sum({<TransCodeKey= Code>} 1)'

if you want to copy Code selections into TransCodeKey

Re: Forcing selection in listbox according to item selection in other listbox with macro

rwunderlich — Wed, 14 Sep 2011 17:57:36 GMT

Attached is a sample using Actions instead of macros.

The basic select expression is:

'("' & concat(DISTINCT Code, '"|"') & '")'

That will shadow all possible values of Code

In the sample, I'm also testing if there are any actual selections.

=if(GetSelectedCount(Code)>0

,'("' & concat(DISTINCT Code, '"|"') & '")'

,''

)

That will shadow only selected values of Code.

-Rob

http://robwunderlich.com

Forcing selection in listbox according to item selection in other listbox with macro

Wed, 14 Sep 2011 18:24:08 GMT

Rob,

If you are saying that if I select something in the "Code" field that the "Other Code" field will also get selected, it doesn't for me.

Stephen

Re: Forcing selection in listbox according to item selection in other listbox with macro

rwunderlich — Wed, 14 Sep 2011 18:41:28 GMT

I'm guessing you are using V9? The double quote syntax -- which is only there only to handle the values with spaces -- doesn't seem to work in V9, but it does in V10. You can take out the " and it will work in V9, but only for values without spaces. There may be a form that works for V9, don't know off the top of my head.

-Rob

Re: Forcing selection in listbox according to item selection in other listbox with macro

Wed, 14 Sep 2011 18:47:37 GMT

Yup - on 9. Now works. Thanks for the tip.

Stephen

Forcing selection in listbox according to item selection in other listbox with macro

louwriet — Thu, 15 Sep 2011 11:41:49 GMT

Hi Rob,

I have implemented the action and it works 100% for my senario. In my case these two listboxes needs to have the same items filtered all the time. For instance my two listboxes contains products from two different tables - but products are the same. If i select one or more product in one listbox it will select the same product in other listbox.

But also now if i select a rep that is linked to the one product listbox - rep is linked to only certain products the same products will be selected in the other product listbox.

So what i did was just to implement your suggestion in the document event triggers(OnAnySelect) instead of the field event triggers because my listboxes must always have the same values filtered.

Thank so much for your help.

Regards

Louw

Re: Forcing selection in listbox according to item selection in other listbox with macro

Tue, 08 Apr 2014 13:43:16 GMT

Hello, I implemented your script and it works great, but it doesn't work if I try to extend the selection to another listbox. I put your code on a field event trigger on select, then i tried to add a second trigger to control the selection of another field. The first trigger works the second doesn't. Am I doing something wrong? Is there a way to force selection on more than one listbox?

Thanks in advance.

Nicola