QV loops through too many values in fieldvaluelist

[neutrofl]

Dear Community,

I'm struggling with a simple loop through a fieldvaluelist of months, where QV loops through too many fields which are not in my list of values, causing it to run into errors when loading a nonexistant file. Can you figure out what's happening here?

First I create an Inline load for variables:

_tmpCountry:

LOAD * INLINE [
Country
Country1
Country2
Country3
];

_tmpMonth:
LOAD * INLINE [
Month
2
3
4
5
6
7
8
];

I then loop through countries and months and load files including the month in their filenames:

for each vCountry in fieldvaluelist('Country')

for each vMonth in fieldvaluelist('Month')

Table1:
LOAD Field1,
Field2,
[...]
FROM
Data_20210$(vMonth)_$(vCountry).qvd

next vMonth

next vCountry

The loop itself works perfectly fine and for Country1 everything is ok. But as soon as the loop for Country2 starts, the variable vMonth adds up to 9, which leads to the error:

Error: Cannot open file: '<Path>\Data_202109_Country2.qvd' (System error: Filesystem::FindImpl FindFirst call)

So my question is, why does it change the variable to being 9 although it has a restriction of being between 2 and 8 according to the inline load above? And why does it work for the first country but fails for all the following (I tried to skip the error for country2, but it fails at all the following countries as well)?

I would be very glad if someone could suggest a solution!

Thanks and BR

neut

[neutrofl]

So, I changed the loop to

for vMonth = 2 to 8

next vMonth

which works perfectly fine. But still I wonder what is wrong with doing the same thing with a fieldvaluelist. Does anyone has an answer, just in case I might need it again in another scenario?

Thanks and BR

[marcus_sommer · Accepted solution]

Field-values are independent from the tables in which they were loaded. Means if any load before or now within the loop has a field Month the available values for the field Month may change. In your case the value is only for the file-pattern so you might just change the field-name, like:

t: load num(recno(), '00') as LoadMonth autogenerate month(today()) - 1;

...
for each vMonth in fieldvaluelist('LoadMonth')
...
from Data_2021$(vMonth)_$(vCountry).qvd
...

Depending on your file-structure it may be also useful to use filelist() to loop through the available files. If you couldn't fetch all requirements with the file-pattern you may further include appropriate if-loops to skip unwanted files.

- Marcus