topic Re: Monitor folder for files and display flag in QlikView

Monitor folder for files and display flag

Mon, 18 Feb 2013 13:32:48 GMT

I currently use a straight table that lists a load of data against a Job for example

Date	Job No	Raised by	Salesperson	Customer
01/01/13	A12345	John	Pete	ACME Motors

I would like to create an expression into another column that would lookup in a set file location for a file and return a Y or N (lets say //servername/files/jobs/A12345/POD.pdf)

Date	Job No	Raised by	Salesperson	Customer	Proof of Delivery (New Column)
01/01/13	A12345	John	Pete	ACME Motors	Y/N

Is this something that would be achieveable within QlikView?

Re: Monitor folder for files and display flag

Mon, 18 Feb 2013 13:54:27 GMT

this can't be done "real time"

either You catch in reload script if the files exist

or You use macro to check it (add a trigger to button running macro)

Re: Monitor folder for files and display flag

Mon, 18 Feb 2013 13:56:08 GMT

Id be fine for this to run on the reload, thats what i expected anyway. Any ideas how to do it?

Re: Monitor folder for files and display flag

Gysbert_Wassenaar — Mon, 18 Feb 2013 14:16:11 GMT

Try this:

SET vFileExists=0;

for each file in filelist('\\servername\files\jobs\A12345\POD.pdf')

SET vFileExists=1;

EXIT FOR WHEN 1;

next file

SET file=;

Re: Monitor folder for files and display flag

Mon, 18 Feb 2013 14:29:57 GMT

this wont create a line against a job though would it? I need to be able to create a flag against every job number, there are hundreds.

Ideally the location to be searching should be something like

\\servername\files\jobs\[Job Number]\*POD*.pdf

Re: Monitor folder for files and display flag

Gysbert_Wassenaar — Mon, 18 Feb 2013 15:09:07 GMT

Oh, not just one file. Ok, maybe this then:

Set vConcatenate = ;

sub ScanFolder(Root)

for each FileExtension in 'pdf'

for each FoundFile in filelist( Root & '\*.' & FileExtension)

JobFileList:

$(vConcatenate)

LOAD '$(FoundFile)' as JobFile, subfield(filedir(),'\',-1) as Job

Set vConcatenate = Concatenate;

next FoundFile

next FileExtension

for each SubDirectory in dirlist( Root & '\*' )

call ScanFolder(SubDirectory)

next SubDirectory

end sub

Call ScanFolder('\\servername\files\jobs\') ;

Re: Monitor folder for files and display flag

rwunderlich — Mon, 18 Feb 2013 20:33:31 GMT

You could do it like this:

if(len(FileSize('//servername/files/jobs/' & [Job No] & '/POD.pdf')) > 0,'Y', 'N') as HasPOD

-Rob

http://robwunderlich.com

Re: Monitor folder for files and display flag

Tue, 19 Feb 2013 09:45:47 GMT

Thanks Rob, that has worked perfectly, great work!