Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I'm having a list of Excel files in a folder. I'm trying to load a selection of these files. But I'm getting stuck here since a simple wildcard load isn't getting me anywhere because the names of the files look very alike.
Here's the list of files in the folder:
01022018 aantal op peildatum.xls
01022018 bedrijf 1 aantal op peildatum.xls
01022018 bedrijf 2 aantal op peildatum.xls
01022018 bedrijf 3 aantal op peildatum.xls
01022018 bedrijf 4 aantal op peildatum.xls
01022018 bedrijf 5 aantal op peildatum.xls
01022019 aantal op peildatum.xls
01022019 bedrijf 1 aantal op peildatum.xls
01022019 bedrijf 2 aantal op peildatum.xls
01022019 bedrijf 3 aantal op peildatum.xls
01022019 bedrijf 4 aantal op peildatum.xls
01022019 bedrijf 5 aantal op peildatum.xls
...
I want to load only the files that do not have the word 'company' in it. What's the best way to do this in Qlik Sense?
Thanks for helping!
Company = bedrijf
You could use a for each in filelist approach and within them you could check for the right files. It means something like this:
for each vFile in filelist('path\files*.xls')
if not wildmatch('$(vFile)', 'bedrijf') then
load * from $(vFile) (biff, ...);
end if
next
More to the logic could you find in the help by for each or here: Loops in the Script.
- Marcus
I think you need to add the wildcard asterisks here:
for each vFile in filelist('path\files*.xls')
if not wildmatch('$(vFile)', '*bedrijf*') then
load * from $(vFile) (biff, ...);
end if
next
Of course you are right, I was too much in hurry ...
- Marcus
Guys,
Thanks a lot so far. I tried using a loop but it's giving an error. Here's the script that I'm using now:
for each vFile in filelist('[lib://Qlik Sense datafolder (concern_lankhorm)/Proalert\* aantal op peildatum.xls]')
if not wildmatch('$(vFile)', '*bedrijf*') then
load * from $(vFile) (biff, embedded labels, Header is 5 lines);
end if
next
exit script;
The error is:
Unexpected token: 'concern_lankhorm', expected one of: 'codepage', 'Comment', 'biff', 'dif', 'fix', 'html', 'json', ...
But I did set the (biff...) stuff. So I though it should work. It keeps complaining if I change it to (biff, no labels) for example.
Any ideas?
Thanks again,
Mike
There is no sheet specified within the file-format, something like ... table is Sheet1$ ...
- Marcus
Hi Marcus,
There's no sheet specified because the sheets all have different names. If I leave out the sheet names it used to work this way (when I don't use a for each loop).
Mike
I'm not absolutely sure yet but if I remember correctly then leaving the sheetname to get the first sheet worked by xlsx-files and by using xls-files it should be table is @1. Just give it a try.
- Marcus