Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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

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

     NEXT

     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

screenshot.PNG

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

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

View solution in original post

13 Replies
Highlighted
MVP
MVP

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

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

     NEXT

     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

Highlighted

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

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
MVP & Luminary
MVP & Luminary

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

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

-Rob

Highlighted
Creator
Creator

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

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

Highlighted
Creator
Creator

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

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

Highlighted
Creator
Creator

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

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

Highlighted
MVP
MVP

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

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

MVP & Luminary
MVP & Luminary

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

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

View solution in original post

Highlighted
Not applicable

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

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