topic Only Load if 1 file in location in QlikView

Only Load if 1 file in location

davyqliks — Sat, 16 Nov 2024 00:24:08 GMT

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.

Re: Only Load if 1 file in location

rubenmarin — Tue, 30 Jun 2020 15:59:02 GMT

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/ScriptControlStatements/For%20Each.htm

Re: Only Load if 1 file in location

davyqliks — Wed, 01 Jul 2020 08:43:39 GMT

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

Daniel

Re: Only Load if 1 file in location

davyqliks — Wed, 01 Jul 2020 15:36:07 GMT

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

Re: Only Load if 1 file in location

rubenmarin — Thu, 02 Jul 2020 06:06:05 GMT

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.

Re: Only Load if 1 file in location

davyqliks — Thu, 02 Jul 2020 15:27:10 GMT

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

Re: Only Load if 1 file in location

Brett_Bleess — Wed, 15 Jul 2020 13:27:03 GMT

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_SupportingTasks_ExternalPrograms_General.htm

Regards,
Brett