Monitor folder for files and display flag

[#1]

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?

[#2]

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)

[#3]

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

[Gysbert_Wassenaar]

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=;

[#5]

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

[Gysbert_Wassenaar]

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\') ;

[rwunderlich · Accepted solution]

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

[#8]

Thanks Rob, that has worked perfectly, great work!