6 Replies Latest reply: Sep 21, 2018 4:38 AM by Khalil Foudhaily RSS

    How to load last 12 months files dynamically

    Mahitha M

      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.

        • Re: How to load last 12 months files dynamically
          Khalil Foudhaily

          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

          • Re: How to load last 12 months files dynamically
            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.

              • Re: How to load last 12 months files dynamically
                Khalil Foudhaily

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

                  • Re: How to load last 12 months files dynamically
                    Mahitha M

                    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

                      • Re: How to load last 12 months files dynamically
                        Khalil Foudhaily

                        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

                  • Re: How to load last 12 months files dynamically
                    Marina Dor

                    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/FileBaseName.htm)

                     

                    //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