How to load last 12 months files dynamically

[mahitham]

Hi Experts,

Can any one please help me on below requirement.

In a Folder I have the files like below

SALEIN_InputBranch_20170119.csv

SALEIN_InputBranch_20170120.csv

SALEIN_InputBranch_20170121.csv

'

'

SALEIN_InputBranch_20180830.csv

Monthly some files are updating into this folder randomly.

Now loading these files as SALEIN_InputBranch_*csv

But here need to load only last 12 months files from the folder. These files don't have the date field.

From filename need to extract the date and from maximum date need to load last 12 months files.

Please help me on this.

Thanks in advance.

[kfoudhaily]

Hello,

please try this;

LET vDateLimitMax  = DATE(today(),'YYYYMMDD') ;

LET vDateLimitMin  = DATE(AddYears(today(),-1),'YYYYMMDD');

SET FileDate = '';

trace loop start;

trace;

for each File in filelist ('*.csv')

    SET sFile    = '$(File)';

    LET FileDate = date#(left(right(sFile,12),8),'YYYYMMDD');

    trace *** processing $(File) dated $(FileDate);

    IF  $(FileDate) >= $(vDateLimitMin) THEN

         trace file is less than 12 months old;

         Directory;

         TABLE:

         LOAD * FROM [$(sFile)](txt, codepage is 1252, no labels, delimiter is ';', msq);

         trace file loaded;

         trace;

    ELSE

         trace file is more than 12 months old;

         trace file ignored;

           trace;

    ENDIF

next File

trace loop end;

exit script;

//--

regards,

Khalil FOUDHAILY

[DavidŠtorek]

Hi,

are files edited when they are inserted into folder? If no you can use Filetime() to create dete field and based on that filter your data.

[kfoudhaily]

@mahitham

not knowing for sure if file date is the same as file creation date the variable should contain the date in the file's name:

LET FileDate = date#(left(right(sFile,12),8),'YYYYMMDD');

[marinadorcassio1]

Hi Mahitha,

I assume the part in bold "SALEIN_InputBranch_20170120.csv" depends on the date.

A function exists in order to get the file name : FileBaseName() (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/FileFunctions/...)

//will list all the name of your files in your folder

FILELIST :

LOAD

     FileBaseName() as FILES,

     FileName() as "FileName",

     If (Date(SubField(FileBaseName(), '_' ,3), 'DD/MM/YYYY') > Addyear(today(),-1), 1, 0) as FLAG

     //If the string YYYYMMDD in the name of your field, transforms as DD/MM/YYYY is posterior to a year      //before today, 1, else 0

FROM yourfolder\*.*;

FILELISTTOLOAD:

LOAD

     distinct "FileName"

Resident FILELIST where FLAG=1;

Drop table FILELIST ;

FOR EACH File in FieldValueList('FileName')

$(Include=yourfolder\$(File));

next;

I didn't test it but that's the idea.

Good luck,

Regards,

Marina

[mahitham]

Hi kfoudhaily,

The Date within the file is not the File creation date.

In one of the folder placing all historical CVS files are loading.

I have created some sample csv files from 20170101 to 20180901 files. From these files need to load only last 12 months files i.e., 20171001 to 20180901 files dynamically based on date present in the file name.

I have tried the code which you have provided in the below attached sample app. But I am getting the below error.

Please help me on this.

Please find the below attached sample files and sample app.

Thanks in advance.

[kfoudhaily · Accepted solution]

hello, sorry I didn't pay attention it was qliksence, I worked out the script on qlikview.

I have just adapted it;

LET vDateLimitMax  = DATE(today(),'YYYYMMDD') ;

LET vDateLimitMin  = DATE(AddYears(today(),-1),'YYYYMMDD');

SET FileDate = '';

trace loop start;

trace;

for each File in filelist ('lib://SALEIN_InputBranch Files/*.csv') // to be edited if necessary

    SET sFile    = '$(File)';

    LET FileDate = date#(left(right(sFile,12),8),'YYYYMMDD');

    trace *** processing $(File) dated $(FileDate);

    IF  $(FileDate) >= $(vDateLimitMin) THEN

        trace file is less than 12 months old;

        Directory;

        TABLE:

        LOAD * FROM [$(sFile)](txt, codepage is 28591, no labels, delimiter is '\t', msq);

        trace file loaded;

        trace;

    ELSE

        trace file is more than 12 months old;

        trace file ignored;

        trace;

    ENDIF

next File

trace loop end;

exit script;

please notice, the script calculates 12 months starting from today's date (today() = day of reload)

here a screen of the loading result;

please don't forget to mark response as correct if it's ok for you