Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi,
Have a look at the attachment on this link.
http://community.qlik.com/thread/77235?tstart=0
Regards,
Kaushik Solanki
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.
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’)
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;
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.)