topic Load files from folder with exceptions in QlikView

Load files from folder with exceptions

nigel987 — Mon, 09 May 2016 08:43:28 GMT

Hi,

I want to read all files from a folder, except one file (named AdType.qvd).

First I used the following statement to make sure that loading all file works.

set pathQVDs = '..\QVDsTest\';

for each File in filelist(pathQVDs& '*.qvd')

load * from [$(File)] (qvd);

next File;

ThenI tried to include an if condition so that all files except AdType.qvd are loaded.

set pathQVDs = '..\QVDsTest\';

for each File in filelist(pathQVDs& '*.qvd')

if FileName('$(File)') <> 'AdType.qvd' then

load * from [$(File)] (qvd);

end if;

next File;

Still, ALL files are loaded (including AdType.qvd).

I tested

if FileSize(vFile) > 500000

to make sure that the if clause is correct. It worked. So I assume that I do not state the FileName correct. I tried several things (I used FileBaseName, FilePath) but couldn't manage to get the syntax right.

Any suggestions? Thanks!

Nigel

Re: Load files from folder with exceptions

Peter_Cammaert — Mon, 09 May 2016 08:51:35 GMT

FileName() will only return a value during the loading of a file (e.g. when a LOAD statement has the file open). It doesn't take any parameters. See the QV Desktop Help article on FileName().

Since inside the loop, variable File will already contain the full path to the current file, you can easily check for the base file name using the SubField() function. Try this:

IF (SubField(File, '\', -1) <> 'AdType.qvd') THEN

Best,

Peter

Re: Load files from folder with exceptions

florentina_doga — Mon, 09 May 2016 08:54:49 GMT

try this

set pathQVDs = '..\folder\';

for each File in filelist(pathQVDs& '*.qvd')

let fis_compar=subfield(File,'\',-1);

if '$(fis_compar)' <> 'aa2.qvd' then

load *,'$(File)' as fis from [$(File)] (qvd);

end if;

next File;

Re: Load files from folder with exceptions

nigel987 — Mon, 09 May 2016 08:54:53 GMT

Thank you for the explanation Peter!

I used the following and it worked!

for each File in filelist(pathQVDs& '*.qvd')

if (SubField(File, '\', -1) <> 'AdType.qvd') then

load * from [$(File)] (qvd);

end if;

next File;

Re: Load files from folder with exceptions

tamilarasu — Mon, 09 May 2016 08:57:54 GMT

Try like below,

set pathQVDs = '..\QVDsTest\';

for each File in filelist(pathQVDs& '*.qvd')

if Not Wildmatch('$(File)', '*AdType.qvd') then

load * from [$(File)] (qvd);

end if;

next File;

Re: Load files from folder with exceptions

nigel987 — Mon, 09 May 2016 08:59:54 GMT

I tested it and this works too!

Re: Load files from folder with exceptions

Kushal_Chawda — Mon, 09 May 2016 09:01:25 GMT

try this

LET pathQVDs = '..\QVDsTest\';

for each vFile in filelist('$(pathQVDs)*.qvd')

Files:

subfield('$(vFile)','\',-1) as FileName

next vFile;

FileNames:

noconcatenate

LOAD FileName

Resident Files

where lower(FileName)<>'adtype.qvd';

drop table Files;

Data:

LOAD 1 as Junk

Autogenerate 1;

for i=1 to fieldvaluecount('FileName')

let vFileName = fieldvalue('FileName','$(i)');

concatenate(Data)

LOAD *

FROM $(pathQVDs) $(vFileName) (qvd);

NEXT i

drop field Junk;

Store Data into Path\Final.qvd;

Re: Load files from folder with exceptions

nigel987 — Mon, 09 May 2016 10:54:20 GMT

Hi Kushal,

I already tried the solutions above and that worked fine for me. Thanks for the comment though!