topic Re: For each variable loop through code in QlikView

For each variable loop through code

gerhardl — Fri, 04 Apr 2014 15:49:55 GMT

Hi,

In my load script I set a certain number of variables which I load from an excel file, like_this.

Currently there are always 10 variables.

I then have a macro which creates an excel file for each of the stores (the variables). But I currently use the same code 10 times to create the 10 different files. I need to cater for the fact that there can be any number of variables in the excel file, so I need to loop through this same code as many times as there are variables, and each time increase the number shown in red below:

ActiveDocument.Fields("Store Number").Select vStore1.GetContent.String

'Select 1st Store

File1 = Location & "Referred Apps Store "& vStore1.GetContent.String & " - " & strYear & strMonth & strDay

obj.ExportBiff File1 & ".xls"

ActiveDocument.Fields("Store Number").Select vStore2.GetContent.String

'Select 2nd Store

File2 = Location & "Referred Apps Store "& vStore2.GetContent.String & " - " & strYear & strMonth & strDay

obj.ExportBiff File2 & ".xls"

I've attached the full macro for reference. If someone could perhaps edit the code in the txt file it will really help a lot.

Thanks,

Gerhard

Re: For each variable loop through code

whiteline — Fri, 04 Apr 2014 19:04:41 GMT

Hi.

And so, what is the problem ? It seems that writing a loop on VB is not a QlikView question.

Use loop, one set of variables and string functions to dynamically get the variable names 'variableN'.

Re: For each variable loop through code

gerhardl — Fri, 04 Apr 2014 19:26:47 GMT

Hi,

I would not have asked if I knew how to do that. And there are literally hundreds of VB questions on here, because it is VB on QlikView.

Your answer is a little vague for me but I appreciate the response. If someone could help me with the code I would appreciate that even more.

Re: For each variable loop through code

whiteline — Sat, 05 Apr 2014 10:05:16 GMT

Hi.

Yes, but your question is really refer to VB syntax.

Post your tries with the loop.

Re: For each variable loop through code

MarcoWedel — Sat, 05 Apr 2014 18:44:41 GMT

Hallo Gerhard,

what are you doing with those variables?

Maybe there is a solution without creating multiple variables?

regards

Marco

Re: For each variable loop through code

gerhardl — Mon, 07 Apr 2014 06:20:19 GMT

Hi Marco,

The variables are store numbers, and for each of the stores a report listing all outstanding applications are created every morning and then emailed to each store individually.

We have hundreds of stores, but this report goes only to 10 or so stores (those with the most applications), so I want business users to be able to choose which stores get the reports - they can do this simply by updating the store number and email address in the excel file.

It works perfectly, except that it HAS to be 10 stores at the moment - I want the user to be able to add or remove stores, so the QlikView macro needs to loop X number of times, based on how many variables were loaded.

Regards,

Gerhard

Re: For each variable loop through code

mattydolan — Thu, 03 Jan 2019 15:51:25 GMT

In case anyone else finds this via google.

Use: Document.GetVariableDescriptions

APIGuide has an example (attached)