dynamic iteration between files

[sai_12]

Hi all,

we are working on a report which uses last 3 years of data. so we create variables in load script like our financial year from end of march

vyear=if(num(month(today()-1) ,'00')>3,right(num(year(today()-1),'00'),2),right(num(year(today()-1) ,'00'),2)-1);

vcurrentyear=$(vyear)&$(vyear)+1;

vpastyear=$(vyear)-1&$(vyear)

v2yearago=$(vyear)-2&$(vyear)-1

our data files are of format ->filename2223 which means file for financial year 22-23

we currently use some thing like this

load a,

b,

c

from filename$(vcurrentyear).qvd

concatenate

load a,

b,

c

from filename$(vpastyear).qvd  so on can we use a for loop to optimise this

something like we only load once  but have to concatenate when ever the loop ends

like 

for var in list(vcurrentyear,vpastyear,v2yearago):

concatenate

load a,

b,

b

from filename$var;

Thank you

[Vegar]

What about this approach that is only lookin at the first two digits of your year string.

Data:

Load * 

inline [ID];

For year = right(year(today()-1)-1,2) to right(year(today()-1)+1,2)

   Concatenate(Data)

   LOAD 

   *,

   FileName() as source_file 

FROM 

   filename$(year)??.qvd (qvd)

;

next year