topic Re: How do I specify Load From filename greater than X? in QlikView

How do I specify Load From filename greater than X?

stevelord — Tue, 09 Apr 2013 17:24:17 GMT

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?

kaushiknsolanki — Tue, 09 Apr 2013 18:25:54 GMT

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?

stevelord — Tue, 09 Apr 2013 19:13:00 GMT

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?

stevelord — Tue, 09 Apr 2013 19:43:29 GMT

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?

stevelord — Wed, 10 Apr 2013 15:07:46 GMT

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?

stevelord — Wed, 10 Apr 2013 16:59:29 GMT

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