6 Replies Latest reply: Nov 17, 2014 12:37 PM by Enrique Herranz RSS

    Oldest Excel file LOADING

    Enrique Herranz

      Hi all,

      I have a folder where I keep a bunch of snapshot data in excel files. The way I name the files is : YYYYMMDD_Data.xlsx

      How could I tell Qlikview to load only the oldest file from that folder in my script ? ( by oldest I mean just looking at the string YYYYMMDD )

      Thanks for any trick to do this

      Enrique

        • Re: Oldest Excel file LOADING
          sree anaarasi

          Hi

          Try this

           

          SalesFolder:   //Here Your getting all the file names that are present in the source folder//

          LOAD

              // RecNo() AS Records,

              FILENAME() AS KEY

          FROM

          [..\DATA\SALESREPORT *]

          (ooxml, embedded labels, table is Sales);

           

           

          MAX_FILE:  // Using resident your loading only the file that has minimum Timestamp//

          LOAD

          Minstring(KEY)  AS MAX_FILE

          RESIDENT SalesFolder;

           

           

          LET vMaxFile=peek('MAX_FILE','-1','MAX_FILE');  // Storing that (maxfile/min File) in a variable//

          DROP TABLE SalesFolder;

           

           

          SalesFolderWithMaxDate: // loading only the file that has max timestamp from the source folder//

          lOAD *

          FROM

          [..\DATA\$(vMaxFile)]

          (ooxml, embedded labels, table is Sales);

           

           

           

           

          /*  U can Create a task by daily or hourly ( On qlikview Server) how your files are updated on the source folder */

           

          Or go through this link :  QlikView should pull automatically from "New Updated Excel Sheet" by latest date.

           

          Let me know if this works

           

          Thanks

          • Re: Oldest Excel file LOADING
            Alessandro Saccone

            TempTab:

            Load Left(FileBaseName() ,8) as myFile from 'C:\.....\......\*.*';

             

            MinVal:

            Load min(myFile) as minimum From TempTab group by myFile;

            Let mini = peek('minimum',0,'MinVal');

             

            load now from your file xlsx as follow:

             

            Load * From .... $(mini)_data.xlsx .....

             

            let me know

            • Re: Oldest Excel file LOADING
              Rob Wunderlich

              It looks like  your file naming convention of YYYYMMDD will make the oldest file be first in sort order, so I think the simplest may be:

               

              FOR Each file in FileList('*_data.txt')

               

                LOAD...

                FROM  [$(file)]

                (biff,.....);

               

                Exit For     // Exit the loop after loading first file

              NEXT file

               

              -Rob

              http://masterssummit.com

              http://robwunderlich.com

                • Re: Oldest Excel file LOADING
                  Enrique Herranz

                  Sorry Rob, my mistake, where I said oldest file I should have said newest file. I am interested in loading only the newest file in the folder. By the way on your proposed script, last line where you add EXIT FOR ...would I need to add "1" at the end ? ( so the loop stops after the first file has been loaded )...or it is just enough to leave it like that ( EXIT FOR ).

                  Thanks a lot for your time and help

                  Enrique

                    • Re: Re: Oldest Excel file LOADING
                      Rob Wunderlich

                      When the loop ends $(file) will point to the last (newest) file. So a simple solution would be to let a do-nothing loop run and LOAD $(file) after the loop.

                       

                      FOR Each file in FileList('*_data.txt')

                      NEXT file

                      LOAD...

                      FROM  [$(file)]

                      (biff,.....);


                      -Rob

                      http://masterssummit.com

                      http://robwunderlich.com

                        • Re: Oldest Excel file LOADING
                          Enrique Herranz

                          Thank you very much guys for your help. I managed to follow your suggestions ( and the quickest way provided by Rob ). I added some calculated fields , derived from the file name to have my datestamp in the resulting table as well :

                          Script :

                          FOR Each file in FileList( '*_Activ_EU.xlsx' )

                          NEXT file

                          LOAD

                          left(Filename(),8)                                                                      as DateStamp_string,

                          Date(date#(left(Filename(),8),'YYYYMMDD'), 'DD-MMM-YY')    as DS_Date,

                          Year(date#(left(Filename(),8),'YYYYMMDD'))                           as DS_Year,

                          Month(date#(left(Filename(),8),'YYYYMMDD'))                         as DS_Month,

                          dual('Q0'&ceil(month(date#(left(Filename(),8),'YYYYMMDD'))/3),ceil(month(date#(left(Filename(),8),'YYYYMMDD'))/3))     as DS_QTR

                          FROM [$(file)]

                          (ooxml, embedded labels, table is Sheet1);

                           

                          Do you know any shortcut for above scenario where you need to add several calculated fields basically using the same original field, in this case being  left(Filename(),8) ? I thought of maybe just loading this calculated field first ( alone ) ...then build the other fields from this one ( already aliased as DateStamp_string ) using a resident load ? Any other ( better, faster ) ideas ?

                          Thanks again for your time and help

                          Enrique