Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
mahitham
Creator II
Creator II

How to load last 12 months files dynamically

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.

1 Solution

Accepted Solutions
kfoudhaily
Partner - Creator III
Partner - Creator III

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;

Capture.PNG

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

QlikView Qlik Sense consultant

View solution in original post

6 Replies
kfoudhaily
Partner - Creator III
Partner - Creator III

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

QlikView Qlik Sense consultant
DavidŠtorek
Creator III
Creator III

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
Partner - Creator III
Partner - Creator III

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

QlikView Qlik Sense consultant
marinadorcassio1
Partner - Creator
Partner - Creator

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
Creator II
Creator II
Author

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.


error.png

kfoudhaily
Partner - Creator III
Partner - Creator III

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;

Capture.PNG

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

QlikView Qlik Sense consultant