Only Load if 1 file in location

[davyqliks]

Hi All,

I have a load where we receive 1 file a day. this must be loaded and saved before the following days file is then loaded.
This is to capture data where invoice lines are removed from the data......

Anyway, in short the automation that creates my source data from a local erp export occasionally fails to connect to the ftp location and then the next day there will be 2 files which load and breaks my file by file logic in the load.

QUESTION:

I load the data as *.xlsx as the data file has a timestamp, but i want to only load data when there is one file in the location, if there is 2 or more files in the location i would like the load to fail so i can then load manually in sequence.

Is there a way to script so that the load only runs when there is one file for import?

I have searched and not been able to find anything related. your expertise on this would be much appreciated.

Thanks in advance

Daniel.

[rubenmarin]

Hi, there are many possibilities, I think one simple way to do it is adding a Filename() field, this will store the filename.

After the process you can load a table with the different filenames and if ther is more than one cancel the load

// Read table adding filename field
LOAD
   ...
   FileNAme() as FileName
...;

// Check different FileNames loaded
tmpFilesLoaded:
LOAD Count(Distinct Filename) as FileNames
Resident TableName;

// If there is more than one different file anme throw an erro to cancel load
LET vFileNames = Peek('FileNames',0,'tmpFilesLoaded')
IF $(vFileNames)>1 THEN
  ThrowError
ENDIF

// Drop auxiliary table and field
DROP Table tmpFilesLoaded;
DROP Field Filename;

 You can also create a bucle that reads all files, check example 2 of this help page that shows how to do that bucle: https://help.qlik.com/es-ES/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Scrip...

[davyqliks]

Hi

Thanks for this, i will try it today and feedback asap.

Daniel

[davyqliks · Accepted solution]

Hi,

Thanks again for the reply.

I found i had to change the capitalisation for Filename to FileName and add ; after the let statement and end if

But i cannot get the coding right for the Throw Error Part.

i tried with 'Then Exit script' but when loading 2 files the Filenames returned 2 in the document.

Here is the script i ended with, with no errors on load:

Sales:
LOAD
*,
FileName() as FileName

FROM
[**********************************************LUK*.xlsx]
(ooxml, embedded labels);

tmpFilesLoaded:
LOAD Count(Distinct Filename) as FileNames
Resident Sales;

LET vFileNames = Peek('FileNames',0,'tmpFilesLoaded');
IF $(vFileNames)>1 THEN; EXIT SCRIPT

ENDIF;

DROP Table tmpFilesLoaded;
DROP Field FileName;

please can you show how you wrote the The throw error section?

thank you so much for your advise on this.

Daniel

[rubenmarin]

Hi, if you use Exit script the app will load all data until it found the exit script clause, if you literally write 'throw error' (without quotes) or any other non-existant sentence like 'jmaisacsmf' it will return an error and not load anything.

[davyqliks]

Thanks for this,

the error in syntax confused me but i see when one file loaded the load is fine and when a second file loaded the Error stops the script, thanks alot.

Daniel

[Brett_Bleess]

Just throwing this one out there too, there is a fairly new 'File Exists' trigger for Publisher too, which you may not have known, this may be easier.  The trick is to be sure once the task triggers due to the file being there, you run a secondary task to delete the file, so it does not keep triggering the task...

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/QMC/Content/QV_QMC/QMC_System_SupportingTa...

Regards,
Brett