Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Find out which files exist in a directory?

Hi,

there is a directory where there are eleven files, sent by the customer every morning and just saved in that directory by the people in another team - I cannot see it.

In QlikView, there used to be a LOAD with wildcard to load all of these files - they are all named in the format

>>> *für* <<< (there is one > F2_Montag für Mittwoch <, one >F2_Mittwoch für Freitag < and so on, and one >Gesamt für Packrückstand< - so the formulation with the asterisk loaded them all.

<=> The issue used to be that every so often, this LOAD failed and because I didn't see any details in the app-log, I - or my colleague - then had to look in the directory and open every one of those 11 lists to see in which one the error was ...

I got fed up with this and resolved the LOAD into 11 individual ones which was supposed to save us a lot of time.

The issue now is, one of the 11 lists that, acc. to the schema, should have been there, was not - and there is no one available today to tell me whether it is usually there or not. I deactivated that particular LOAD for now so it worked, but tomorrow that file might be there again ...

A solution would be just deactivating the ErrorMode altogether, but then that work would have been to no end, I would not get any info about errors ... So I have to first find out (in a LOAD with ErrorMode=0) which of the files are there and which are not

and then LOAD (with ErrorMode=1) only those which are available.

I have already had a look at the available file_functions, but none seems to do just what I need. Can anybody help me there,l please?

Thanks a lot!

Best regards,

DataNibbler

17 Replies
shane_spencer
Specialist
Specialist

Yeah, just use the code I provided above but instead of loading just the First 1 do a Load * of the files. To give more specific advice I would have to see your raw data and script but if all the files are the same you can load them using a Loop. If they are difference then us the If command above to load each file in turn only if it exists and use the Loop to create a log file with a list of the file names.

p.s. Don't forget to "Like" my comments if they've been helpful.

datanibbler
Champion
Champion
Author


Hi Shane,

one more question:

The code is now finally working fine - I cannot check the exact figures, but it looks ok - just the same as the old "Wildcard-LOAD".

The remaining issue is, when I fill in the original directory where those lists usually are (into that FileList, I will post it), then nothing happens - the code is just not executed, the log continues straight away with the next table in line.

Yesterday I copied those lists to a directory on the QlikView_server that I can access - there it works fine and all the information is in the log.

(the access, though, is not an issue since the reloading is being done by a "QlikView_user" who does have access to the original directory.

The original code (which used to work, but gave me no detailed debugging information) looked like this:

BMW_Bedarfe:

LOAD

         [list_of_fields]
FROM
[\\rgb1sfs201\Regensburg\MPL\05-Liefertreue_PR_Listen\F2_*für*.xlsx]
(ooxml, embedded labels);

The new code (which works, but not out of that directory) looks like this:

v_fileformat_F2 = '*für*';
For each File in FileList('..\99_Resources\MPL\F2\$(v_fileformat_F2)')

BMW_Bedarfe:
LOAD
    [list_of_fields]
FROM
$(File)
(
ooxml, embedded labels);

Next File

The code, such as I would like to use it (but which, for some reason, doesn't yet work) only differs in the first line, so I post that here:

For each File in FileList('[\\rgb1sfs201\Regensburg\MPL\05-Liefertreue_PR_Listen\$(v_fileformat_F2)]')

Can you make a rhyme of this?

Thanks a lot!

Best regards,

DataNibbler

shane_spencer
Specialist
Specialist

The most obvious answer is that here you are using relative location

'..\99_Resources\MPL\F2\$(v_fileformat_F2)'


The ".." tells you to go up a level in Unix and Dos. You should always fully qualify your path


'\\rgb1sfs201\Regensburg\MPL\05-Liefertreue_PR_Listen\$(v_fileformat_F2)'


So try updating this line to read:


For each File in FileList('\\rgb1sfs201\Regensburg\MPL\05-Liefertreue_PR_Listen\$(v_fileformat_F2)')


If that doesn't work check you path is correct by copying the address in to explorer \\rgb1sfs201\Regensburg\MPL\05-Liefertreue_PR_Listen\

If you're still having issues please upload your script / document and I'll try to run it.


Please hit the button on any of my posts that are helpful, as it will enhance my "reputation". Cheers

datanibbler
Champion
Champion
Author

Hi Shane,

relative or absolute path actually makes no difference - all the paths in the "live" apps are absolute - but when I replace that

>> '..\99_Resources_MPL\F2' <<

by

>> '\\rgb1app202\production\resources' <<

it works just the same

<=> but it does not work with the original directory.

I have already posted my code. It just lacks some sample files - but that should be easy. I can make you some lists matching this name_format with something in them.

Thanks a lot!

Best regards,

DataNibbler

shane_spencer
Specialist
Specialist

QlikView can be very fickle but I've got this working. It creates two tables, one from you files and the other a list of files loaded.

datanibbler
Champion
Champion
Author

Cool!

If I understand that correctly, that >directory< command tells QlikView in which directory to look for that FileList in the following LOAD_statement.

I'll try this.

P.S.: One thing is not quite clear to me, though: The order - does that >directory< command have to go before or after the >For each ...< command? You seem to have that >For each ...< twice, with the >directory< inbetween, that confuses me ...

P.P.S.: And one more thing: Can I somehow reset that >directory< command? Apparently, there were some relative paths in the tables loaded afterwards, and they got mixed up with that and broke. I'm replacing them with absolute paths now. Still ...

shane_spencer
Specialist
Specialist

Before. You tell QlikView which directory to start in then the For Each actions that directory.

Replace the relative paths with absolute paths, it's a much better practise.

datanibbler
Champion
Champion
Author


Hi Shane,

now this is solved at long last. In the meantime, we had a complete Crash - nothing loaded anymore, the automated reload_schedule went on strike and I couldn't start anything manually in QMC, either - then my boss restarted the QDS_service and all was well again 😉 We had that a few times before, but twice today - that should just not happen.

Now, however, the code executes fine.

So this chapter is finally closed.

Have a nice weekend!