topic Re: VB Script to Select a Month and Year in QlikView

VB Script to Select a Month and Year

Thu, 12 Jul 2012 16:15:19 GMT

Hi,

I was wondering if it was possible to produce some VB Script which would enable me to have a dropdown box where I can select the month and year:

e.g.

Month:

Jan

Feb

Mar

Apr

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Year:

2010

2011

2012

2013

Once, I've made these selections - I want this to become a variable value vFilePath = "C:\My Documents\Data (Month Year)" which I can put in my loading script. i.e. if I choose Jul and 2012, then vFilePath becomes C:\My Documents\Data (Jul 2012)

Is this possible to do?

Thanks. Any help would be greatly appreciated.

Re: VB Script to Select a Month and Year

hic — Thu, 12 Jul 2012 22:06:42 GMT

You do not need a macro for this.

Do it this way instead:

1) Have this in the script:

Dates:

Load

Date(AddMonths(MakeDate(2010,1),recno()-1),'MMM YYYY') as YearMonth

autogenerate 48;

2) Display this field in the UI.

3) Create an inputbox with one variable: "vFilePath"

4) Set "Input box properties -> Constraints -> Settings for selected variable -> Value" to

='C:\My Documents\Data (' & MinString(YearMonth) & ')'

Now you can select your month in a list box and getyour variable that you can use in the script.

HIC

Re: VB Script to Select a Month and Year

Fri, 13 Jul 2012 08:18:43 GMT

Hi Henric,

Thanks for getting back to me. This doesn't work when I reference vFilePath in the load script (i.e. $(vFilePath)).

When I run the script, it comes up with an error and shows vFilePath as 'C:\My Documents\Data (' & MinString(YearMonth) & ')' instead of the actual path using the list box when selecting the month/year. Is there anyway this can be copied as text into the variable?

Thanks.

Re: VB Script to Select a Month and Year

jonathandienst — Fri, 13 Jul 2012 08:26:25 GMT

Make sure that you include the = sign as first character in the string.

Regards

Jonathan

Re: VB Script to Select a Month and Year

Fri, 13 Jul 2012 08:27:03 GMT

Hi,

Henric is correct...

Create a variable and in the definition section write ='C:\My Documents\Data (' & MinString(YearMonth) & ')' , you have to do it through Variables Overview.

Re: VB Script to Select a Month and Year

Fri, 13 Jul 2012 09:34:18 GMT

Hi,

I've put the equals sign in and it still doesn't work.

I get this error message:

Cannot open file '='C:\My Documents\Data\( '&MinString') The filename, directory name, or volume label syntax is incorrect.

Maybe I'm doing something wrong.

Thanks.

Re: VB Script to Select a Month and Year

hic — Fri, 13 Jul 2012 11:17:01 GMT

You're right. I have tested it and for some reason it does not do what I expected.

Anyway, there is another way to do this without macros.Check the attached file. In it I have an input box where the variable values are constrained to the only possible years/months. When you run the script, these variables can be used to define fields or file paths.

Good luck!

HIC

Re: VB Script to Select a Month and Year

Fri, 13 Jul 2012 11:47:32 GMT

Thanks Henric!! That works now!