Passing value from variable for top select

[#1]

Hi,

I have the following macro:

Sub SelectTop10
'get value from list box and stored it to VShow
set val=ActiveDocument.Fields("SelectTypeOne").GetSelectedValues
Value= val.Item(0).Text
ActiveDocument.Variables("VShow").SetContent value, true

'Rank top 10 base product for sum of shoes
set f = ActiveDocument.GetField ("Product")
f.TopSelect "sum(shoes)", 10


End sub

I would like to substitue "Product" with the variable "VShow". Currently, Qlikview is throwing a Method can't be use for f.TopSelect. Am I missing something from here?

Thank you for checking out my post! 🙂

[#2]

What does your field SelectTypeOne contain? Is that a list of fields?

You say you want to replace Product with VShow, so are you trying to use: set f = ActiveDocument.GetField(VShow) ? If so, you should try: set f = ActiveDocument.GetField($(VShow)).

[#3]

The SelectTypeOne contains text (A,B and C). And yes, I am trying to replace hardcoded "Product" column with VShow which populates after user make a selection in SelecTypeOne.

I tried your example and received Invalid Character.

Thanks again for looking at this 🙂

[#4]

Oh, maybe you can't do a dollar sign expansion at that point.

From your macro, it looks like your setting VShow to be A, B or C. Then you're trying to reference that as a field name, but do you have a field named A, B or C? I'm still unsure about the connection between ShowOneValue and the fields in your application. I'd expect the values for ShowOneValue to be Product and/or other field names in your application. Maybe you could give a basic description of what you're trying to accomplish.

I set up a sample app to test your macro. If I try this: set f = ActiveDocument.GetField (VShow), I get an error, "Object Required: f" I get the same error if I try: set f = ActiveDocument.GetField ("$(VShow)"), so I must be doing something different, because you are getting different errors.

Also, in your macro, when you set Value, you capitalize the first letter. When you set the variable, you use value. Actually, I don't know if VB Script is case sensitive at that point. Have you used an Input Box or otherwise checked the value of VShow before you try to do the TopSelect?

[#5]

You are correct. Sorry I was trying to make a quick reply to your question.

Here's the break down

Listbox SelectTypeOne contains

Product1 (slippers)
Product2 (sneakers)
Product3 (dress shoes)
Shoes (# of pair sold)

I am currently store selected value from ListBox SelectTypeOne into a variable called VShow. I would like to use Vshow in

set f = ActiveDocument.GetField ("Product <--- replace with Vshow")
f.TopSelect "sum(shoes)", 10

So far it gives me error saying that "f" needs to be define.

The other way that I can think of doing this is to do If then else statement. I just don't know how to write IF Statement that look for either selected field in a table or variable...

Something like..

If ActiveDocument.Fields("SelectTypeOne").GetSelectedValueds ="Product1" then

set f = ActiveDocument.GetField ("Product1")
f.TopSelect "sum(shoes)", 10

End if

This statement doesn't work either.l

[#6 · Accepted solution]

Okay, that makes sense.

I was about to suggest an if...then, but it wouldn't be quite as dynamic. I'd take a little from your first macro and combine it with the if...then. Something like this should work:

Sub SelectTop10
'get value from list box and stored it to VShow
set val=ActiveDocument.Fields("SelectTypeOne").GetSelectedValues
Value= val.Item(0).Text
'ActiveDocument.Variables("VShow").SetContent value, true
' variable may not be needed

'Rank top 10 base product for sum of shoes

if Value = 'Product1' then
set f = ActiveDocument.GetField ("Product1")
else if Value = Product2' then
set f = ActiveDocument.GetField ("Product2")
' etc...
end if

f.TopSelect "sum(shoes)", 10

End sub

I just did a little more thinking on this. Do you have separate fields named Slippers, Sneaker, Dress Shoes, etc. (or Product1, Product2, etc)? Or are those values in a field?

This is what I am visualizing:

Product ProductTypes Shoes
Comfy Slips Slippers 5
Air Jordans Sneakers 7
Penny Loafers Dress Shoes 4
Converse ASs Sneakers 4

And then based on the selection, you would want the Top 10 Sneakers by number of shoes. I can't see where each product would be a separate field, which is what this macro would be for. (It's Friday, it wouldn't surprise me if I'm a little slow this morning! )

[#7]

This is exactly what I needed. Thank you so much for your help!!!!!!!!!!!!!

[#8]

Glad to help!