5 Replies Latest reply: Apr 10, 2013 12:59 PM by Steve Lord RSS

    How do I specify Load From filename greater than X?

    Steve Lord

      We have a folder where files are being loaded automatically going forward, and previous old files are a hodgepodge of filetypes and tablenames, so I can't just refer to the whole folder with the *.xlsx wild card.  Additionally, there is a program that 'cleans' this folder by removing files it didn't create and replacing files it did, so their modified/created date attribute is always today.  (And I don't want to make anyone turn off a cleaning program or de-automate their folder.)  Fortunately, the files have the date they were created built into the filename itself like this:  "datafeed-2013_04_09_123456.xlsx".

       

      So at present I have the existing hodgepodge of files loaded individually in my QV script and I want a piece of script that only loads from files with filename "datafeed-*.xls" if filename is greater than "datafeed-2013_04_09_123456.xlsx"

       

      Help is appreciated.  Thanks!

       

      (And I'm keeping the QVW script in a separate folder so the cleaning program doesn't delete it. )

       

      -Steve

       

      PS> I'm working with qlikview personal edition building scripts that refer to a shared drive, so text in body of the message would be great!

        • Re: How do I specify Load From filename greater than X?
          Kaushik Solanki

          Hi,

           

               Have a look at the attachment on this link.

           

               http://community.qlik.com/thread/77235?tstart=0

           

          Regards,

          Kaushik Solanki

            • Re: How do I specify Load From filename greater than X?
              Steve Lord

              Hi Kaushik, can you copy the text out to the body of your post.  I can't read other people's QV files until I get off probationery period and company buys me a license.

              • Re: How do I specify Load From filename greater than X?
                Steve Lord

                Hi All, File isn't pulling data from fthe new files but the error message is gone now.  Am I missing something (I am a newbie crashtraining)

                 

                The error message went away when I put the word Root back in and made the script look like above text (with my own file path in place of Kaushik's)

                 

                Sub

                DoDir(Root)
                For Each Ext In 'xlsx' // filetype to search for in current directory

                          For Each File In FileList (Root & '\datafeed-*.' & Ext)





                     Files:

                    
                Load '$(File)' as Name,

                    
                timestamp#(mid('$(File',11,19),'YYYY-MM-DD_hh-mm-ss') as FileTimeStamp,

                    
                FileTime('$(File)') as FileTime,

                                    
                RangeSum(Peek('FileCount'), 1) as FileCount

                                   
                Autogenerate 1;

                         
                Next File

                    
                Next Ext



                For Each Dir In DirList (Root & '\*')

                         
                Call DoDir('P:\Production\Incentive\_REDEMPTION\InBound\Archive')

                    
                Next Dir

                End Sub



                Call DoDir ('P:\Production\Incentive\_REDEMPTION\InBound\Archive');

                MostRecentFileName:

                LOAD Name AS MostRecentFileName

                RESIDENT Files

                WHERE FileTime > timestamp#('2013-04-01_12-21-35','YYYY-MM-DD_hh-mm-ss');

                LET vFileToLoad = FieldValueCount('MostRecentFileName');



                for each file in FieldValueList('MostRecentFileName')

                MostRecentdata:

                Load [Card Type],

                    
                [Company Name],

                    
                UPPER([Recipient Name]),

                    
                [Recipient Email Address],

                    
                [Item SKU],

                    
                [Item Description],

                    
                [Shipment Type],

                    
                IF(IsNull([LOB Code]) OR Len([LOB Code])=0,'blank',[LOB Code])

                FROM $(file)

                (
                ooxml, embedded labels, table is GC);

                next file



                DROP TABLE MostRecentFileName,Files; 

                 

              • Re: How do I specify Load From filename greater than X?
                Steve Lord

                Thanks it looks like it will all work, just the snag of a script line error related to below.  How can I resolve this?

                 

                Script line error:

                Sub DoDir ('P:\Production\Incentive\_REDEMPTION\InBound\Archive')

                 

                I attached a text copy of the script with my actual values filled in now.

                 

                (I replaced the word Root with the path in the script below.  I tried with and without the apostrophes.  The other lines seem functional where I replaced Root with ‘P:\Production\Incentive\_REDEMPTION\InBound\Archive’)

                  • Re: How do I specify Load From filename greater than X?
                    Steve Lord

                    It is interesting, I can get through with no error message setup as shown (though new test data isn’t brought in), but I noticed a missing ) on the $(File) in the below timestamp# line.  When I fix that, I get error messages about table not found further down and wonder if program simply didn’t continue past that missing ) before.

                     

                    timestamp#(mid('$(File)',10,19),'YYYY-MM-DD_hh-mm-ss') as FileTimeStamp,

                    (I also changed the 17 to 19 when I saw the filenames had hyphens between hh mm ss, and changed underscore to hyphen when I saw that with YYYY MM DD on my end, but those should be superficial/correct changes.)

                    New error messages:

                     

                    Table not found

                    MostRecentFileName:

                    LOAD Name AS MostRecentFileName

                    RESIDENT Files

                    WHERE FileTimeStamp > timestamp#('2013-04-01_12-00-00','YYYY-MM-DD_hh-mm-ss')

                     

                    Script line error:

                    for each file in FieldValueList('MostRecentFileName')

                     

                    Table not found

                    DROP TABLES statement

                     

                    So, follow-up (probably newbie) question is how/where do I make a table of the sort needed?  (Other person said I’d need to do something to create a timestamp field, but it looks like that is handled in one of the versions of the script submitted around here.)