topic Re: Selecting a numeric value from a list box using a macro in QlikView

Selecting a numeric value from a list box using a macro

Thu, 03 Sep 2015 11:49:44 GMT

Hello

I am having problems with selecting a numeric value from a list box using a macro. I have set up a very simple qv model to illustrate the problem. In the attached example you will see that I have :

- A table box showing some simple data (First name, Last name and Age) which I typed into excel and imported into Qlikview

- 2 very simple macros which are called from the 2 buttons

The first macro which selects the first name 'Steven' from the FName list box works fine whereas the 2nd macro which selectes the age 29 from the Age Listbox does not. If I change the line

fv(0).Text = "29" to be fv(0).Text = "Unknown" - this works ok

I assume this is because a text string works ok but a numeric value does not.

Any ideas how I can get the select on a number (ie age 29) to work ?

My macros are listed below and I have attached the qlikview model.

Thanks in advance !

Shirley

Sub SelectFName()

set f = ActiveDocument.Fields("FName")

set fv = f.GetNoValues 'empty array

fv.Add

fv(0).Text = "Steven"

f.SelectValues fv

end sub

Sub SelectAge()

set f = ActiveDocument.Fields("Age")

set fv = f.GetNoValues 'empty array

fv.Add

fv(0).Text = "29"

f.SelectValues fv

end sub

Re: Selecting a numeric value from a list box using a macro

m_woolf — Thu, 03 Sep 2015 19:58:31 GMT

Why not just:

ActiveDocument.Fields("Age").Select 29

Re: Selecting a numeric value from a list box using a macro

marcus_sommer — Thu, 03 Sep 2015 21:39:04 GMT

I'm not sure if you really needs macros for this. Mostly actions could do the same. See also: Macros are Bad.

- Marcus

Re: Selecting a numeric value from a list box using a macro

Sat, 05 Sep 2015 09:54:13 GMT

Hello

Thanks for your answer. It is helpful but in my macro I actually want to select a number of values from the list box - (I just posted on here a very simple qlikview model showing 1 item I want to select).

The way I want it to work is that the user enters a comma separated list of values into an input box and they then click on a button that runs a macro which selects those items in the list box (see screenshot below). I have 2 listboxes and 2 input boxes (1 for age and 1 for FirstName). The Age input box uses the var vAgeList and the FirstName input box uses the var vFNameList. The FirstName button works fine and selects the required items but the age one does not (except if I put 'Unknown' into the input box). So I can only get my method to work with text data - how can I get it to work with numbers ? Please See scripts below :

Thanks Shirley

function getVariable(varName)

set v = ActiveDocument.Variables(varName)

getVariable = v.GetContent.String

end function

Sub SelectFName()

set oFNList = ActiveDocument.Fields("FName")

set FNListValues = oFNList.GetNoValues 'empty array

FNList=Split(getVariable("vFNameList"), ",")

i=0

For Each FN in FNList

FNListValues.Add

FNListValues(i).Text = trim(FN)

i=i+1

oFNList.SelectValues FNListValues

End Sub

Sub SelectAge()

set oAgeList = ActiveDocument.Fields("Age")

set AgeListValues = oAgeList.GetNoValues 'empty array

AgeList=Split(getVariable("vAgeList"), ",")

i=0

For Each Age in AgeList

AgeListValues.Add

AgeListValues(i).Text = trim(Age)

i=i+1

oAgeList.SelectValues AgeListValues

End Sub

Re: Selecting a numeric value from a list box using a macro

Sat, 05 Sep 2015 10:03:46 GMT

Hello Marcus

Thanks for your answer. I know that macros should only be used where necessary - however I put a 'cut-down' version of what I wanted to do as a post to try and simplify it - I should have described the full picture. I am trying to select a number of values from the list box from a comma separated list in an input box. It works fine work with text data but not with numbers ('age' in my example). Any ideas ?

Pls see the full description I've put in the reply to M Woolf. I've actually updated my qlikview model to demonstrate the problem but I'm not sure how to post a qlikview model in a 'reply' here.

thanks for your help

Shirley

Re: Selecting a numeric value from a list box using a macro

marcus_sommer — Sat, 05 Sep 2015 10:10:13 GMT

This is the pdf of a german posting to the same topic: Inputbox not more then 25000 Char's and I think you will find here more hints: https://community.qlik.com/search.jspa?q=inputbox+macro

If your input-listings isn't very big (< 1024 chars) it could be easier to try it with normal selecting-list:

(value1|value2|value3)

or with a selction-action.

- Marcus

Re: Selecting a numeric value from a list box using a macro

Sat, 05 Sep 2015 11:36:48 GMT

Thanks Marcus

Much appreciated

I followed some of the links and eventually found this article which does exactly what I want without a macro !

Search by copy and pasting from an external list - The Qlik Fix! The Qlik Fix!

Shirley