Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I have an application that runs weekly and I am trying to automate qvd creations and reloads.
My scenario:
I have weekly text file that are run over the weekend and are stored as text files. My file names will always be of the form FileName_DDMMMYYYY for every week. For example week 1 will be 03Jun2011, week 2 will be 10Jun2011, week 3 will be 17Jun2011.
Is there a way to create some kind of dynamic variable in my load script to cater for this changing weekly date value, so that I do not have to hardcode my script always.
At the moment I have come up with
let weekdate = date(today() -2, 'DDMMMYYYY');
let week1 = 'FileName_$(weekdate)';
let j=1;
TableName:
Load ........
From
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
The solution I have is not flexible enough and will only work if I run my automation piece on a monday but not on any other day as the dates change making the file name wrong.
Can someone please help me out to get this working? Thanks
Hi,
Then something like the following may do the trick
DatesOfFiles:
LOAD MaxString(Right(FileBaseName(), 8)) AS PossibleDates
FROM
file*.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
LET vLastDate = Peek('PossibleDates', -1);
DROP TABLE DatesOfFiles;
LastDataFile:
LOAD *
FROM file$(vLastDate).txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
Although my guess is it does, I don't know whether this would load faster than the macro one.
Hope that helps.
BI Consultant
Hi,
I have answered the related question on this post.
http://community.qlik.com/message/126205#126205
Regards,
Kaushik Solanki
Hi Kaushik,
Your code is amazing, but I need yourhelp to get it working for just one file in a directory.
I don't want it to look at sub directories at all. I tried making some changes but then the code kept on falling over, because I don't know anything about the kind of scripting you are doing as I am relatively new to scripting in QlikView
Can you please just guide me to make it work by eliminating the For loops so that it just looks for only one file and does not look at sub directories but only the main directory specified.
Thanks
Sub DoDir (Root)
For Each Ext In 'csv' // filetype to search for in current directory
For Each File In FileList (Root & '\MYFILENAME*.' & Ext)
Files:
Load '$(File)' as Name,
FileTime('$(File)') as FileTime,
RangeSum(Peek('FileCount'), 1) as FileCount
Autogenerate 1;
Next File
Next Ext
For Each Dir In DirList (Root & '\*') // search in sundirectories
Call DoDir (Dir)
Next Dir End Sub Call DoDir ('C:\MYFOLDER'); // Starting point
MostRecentFileName:
FIRST 1 LOAD Name AS MostRecentFileName
RESIDENT Files ORDER BY FileTime DESC;
LET vFileToLoad = FieldValueCount('MostRecentFileName');
for each file in FieldValueList('MostRecentFileName')
TableName: LOAD ................... FROM $(file)
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
store TableName into [C\$(file).qvd];
//next file
DROP TABLE MostRecentFileName,Files;
Hi,
Just delete this pat of code.
For Each Dir In DirList (Root & '\*') // search in sundirectories
Call DoDir (Dir)
Next Dir
Regards,
Kaushik Solanki
Hello,
Without the need of that macro, and provided all CSV files are in the same folder you can improve the load performance and do just
Table:
LOAD *
FROM C:\Folder\FileName*.txt (options and delimiters here);
The "*" is valid as a name and it will work within the folder specified.
Hope that helps.
BI Consultant
Hi Kaushik,
Although all code that is posted in the Community is free to use and modify, please refer to the actual sources when you provide code to other people. That's the best way to improve the code if needed and check the bugs it may contain and keep the best possible solution in place.
Thanks and regards.
BI Consultant
Hi,
Miquel is you look at the code i have provided, you will see that i have provided the same code.
By the way thanks for suggession.
Regards,
Kaushik Solanki
Hello,
Ellaborating a bit further my code above, the following should work fine without needs of macro or loops, and likely improving performance in the loading process
LET vLastDate = Date(Today(), 'YYYYMMDD'); // variable with the date you want to start loading
Table:
LOAD Field1
FROM
file*.txt // filename
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)
WHERE Date(Right(FileBaseName(), 8)) >= '$(vLastDate)';
Hope that makes sense.
BI Consultant
Thanks for you help Miguel. My directory contains multiple files, but I only want to load the file for the most recent date alone, that is why i have avoided using the *.
I tried your code above as I am aiming to avoid using macros and loops if possible, but because of the * it picked up other files with the dates I do not want, as I only want to load the file which has the newest info alone.
Thanks
Hi,
Then something like the following may do the trick
DatesOfFiles:
LOAD MaxString(Right(FileBaseName(), 8)) AS PossibleDates
FROM
file*.txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
LET vLastDate = Peek('PossibleDates', -1);
DROP TABLE DatesOfFiles;
LastDataFile:
LOAD *
FROM file$(vLastDate).txt
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
Although my guess is it does, I don't know whether this would load faster than the macro one.
Hope that helps.
BI Consultant