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. )




      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



               Have a look at the attachment on this link.





          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)



                For Each Ext In 'xlsx' // filetype to search for in current directory

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


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


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


                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


                    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.)