topic For ... each in filelist in QlikView

For ... each in filelist

Thu, 23 Feb 2012 08:53:19 GMT

Hello,

very simple and stupid problem, but I'm not able to find a solution. This is my script:

FOR EACH FILE IN FileList('$(DIRBUDGET)budget_201*.xls')

load .......

FROM

$(FILE)

(biff, embedded labels, header is 1 lines, table is Operatori$);

next FILE;

It load all my files from 2010 to 2019... but I need 2009, too, and I don't want to load 2008 and 2007. And cause I'm working on someone else script, I wouldn't want to change the structure of the script. I simply want to add 2009 file.

Thanks

Filippo

For ... each in filelist

christophebrault — Thu, 23 Feb 2012 09:04:14 GMT

Why don't you just load 2009 in another part of script just before or after ?

Dont't use the loop, just :

load .......

FROM

budget_2009.xls // your path to 2009

(biff, embedded labels, header is 1 lines, table is Operatori$);

When you load exactly the same fields and tables will concatenate.

Hope this can solve easily your problem.

For ... each in filelist

Thu, 23 Feb 2012 09:09:58 GMT

Cause if tomorrow someone wants to see 2008 and 2009 sales I have to add a part of script newly. It works but it's not so "lean".

For ... each in filelist

ekech_infomotio — Thu, 23 Feb 2012 09:25:54 GMT

Hi Filippo,

you could use

the following:

set vBaseDir = '\\path\to\your\files\';

set vBaseName = 'budget_';

for each vMonth in '2009', '2010', '2011', ..., '2019'

load

from

$(vBaseDir)$(vBaseName)$(vMonth).xls (biff, embedded labels, header is 1 lines, table is Operatori$);

next vMonth

;

So you can load other months in a very simple way by adding/deleting values to your list

regards,

Edgar

Re: For ... each in filelist

CELAMBARASAN — Thu, 23 Feb 2012 09:31:46 GMT

Hi,

You can try with this

For year=2009 to 2019

load .......

FROM

$(DIRBUDGET)budget_$(year).xls

(biff, embedded labels, header is 1 lines, table is Operatori$);

next;

Hope it helps

Celambarasan

For ... each in filelist

Thu, 23 Feb 2012 09:50:28 GMT

Unfortunately variable FILE is used lots of times. And this loop is used several times. I don't want to create a new loop. I could instead add a second loop, maybe using year variable. What could be the right syntax?

for year = 2009 to 2014

FOR EACH FILE IN FileList('$(DIRBUDGET)budget_$(year).xls')

load ...

next file

next year

Is it correct?

For ... each in filelist

SunilChauhan — Thu, 23 Feb 2012 09:52:46 GMT

use below code

FOR EACH FILE IN FileList('$(DIRBUDGET)budget_201*.xls','$(DIRBUDGET)budget_2009*.xls')

load .......

FROM

$(FILE)

(biff, embedded labels, header is 1 lines, table is Operatori$);

next FILE;

hope this helps

For ... each in filelist

Thu, 23 Feb 2012 09:55:00 GMT

I modified it and now it works as I want.

Thanks you all...

For ... each in filelist

Thu, 23 Feb 2012 09:56:59 GMT

use below code

FOR EACH FILE IN FileList('$(DIRBUDGET)budget_201*.xls','$(DIRBUDGET)budget_2009*.xls')

load .......

FROM

$(FILE)

(biff, embedded labels, header is 1 lines, table is Operatori$);

next FILE;

hope this helps

Hi Sunil,

this was my first attempt, but it doesn't worked.