Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear QlikCommunity,
Please help. I'm stuck after Step 1. I'm sure there's even an easier way to do all this in less "steps". Basically, I'm trying to cut down the number of files and load time.
The below is basically what I want to accomplish:
Daily .csv files located in one folder -> 1) stored as .qvd files in another folder -> 2) consolidated into monthly .qvd files based on Mth_Yr field -> 3) consolidated into master .qvd file
// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //
// / / / / / / Load each csv and store into a qvd in QVDs folder only if not already done / / / / / / / //
// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //
1a) E:\share\QlikView\Common Reference\Data\Compliant Events
CompliantUnix-Interactive_02-19-2015-04-55-00.csv
Compliant-Unix-Interactive_02-20-2015-04-55-00.csv
Daily-Wintel-Interactive_02-26-2015-15-02-00.csv
Daily-Wintel-Interactive_03-25-2015-15-02-00.csv
Wintel-RemoteInteractive_03-04-2015-15-02-20.csv
1b) E:\share\QlikView\Common Reference\Data\Compliant Events\QVDs
CompliantUnix-Interactive_02-19-2015-04-55-00.qvd
Compliant-Unix-Interactive_02-20-2015-04-55-00.qvd
Daily-Wintel-Interactive_02-26-2015-15-02-00.qvd
Daily-Wintel-Interactive_03-25-2015-15-02-00.qvd
Wintel-RemoteInteractive_03-04-2015-15-02-20.qvd
1) Code.....
DIRECTORY E:\share\QlikView\Common Reference\Data\Compliant Events;
FOR Each vFile in FileList('*.csv')
LET vQVD = Replace(Replace(vFile, '.csv', '.qvd'), 'Compliant Events','Compliant Events\QVDs');
IF Alt(FileSize('$(vQVD)'), 0) = 0 then
NextFile:
LOAD
1 as Row_Counter,
DATA_SRC_ID as Event_ID,
SESSION_ID as Session_ID,
Date(MonthStart(SESSION_END_TIME),'MMM-YYYY') as Mth_Yr,
Timestamp(Timestamp#(SESSION_END_TIME,'DD-MMM-YYYY hh:mm:ss'),'MMM DD YYYY hh:mm') as Event_End_Time,
Timestamp#(SESSION_START_TIME,'DD-MMM-YYYY hh:mm:ss') as Created_Timestamp,
USER_ID as User_ID,
FileName() as Source_File
FROM
[$(vFile)]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
STORE NextFile into '$(vQVD)' (qvd);
DROP Table NextFile;
END If
NEXT vFile;
// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //
// / / / / / / / / Load each qvd into appropriate month qvd only if not already done / / / / / / / / / / //
// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //
1b) E:\share\QlikView\Common Reference\Data\Compliant Events\QVDs
CompliantUnix-Interactive_02-19-2015-04-55-00.qvd
Compliant-Unix-Interactive_02-20-2015-04-55-00.qvd
Daily-Wintel-Interactive_02-26-2015-15-02-00.qvd
Daily-Wintel-Interactive_03-25-2015-15-02-00.qvd
Wintel-RemoteInteractive_03-04-2015-15-02-20.qvd
2) E:\share\QlikView\Common Reference\Data\Compliant Events\QVDs
Compliant Events Feb 2015.qvd
Compliant Events Mar 2015.qvd
2) Code.....
? ? ? ?
// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //
// / / / / / / / / / / / / / / / / / / Load each monthly qvd into a master qvd / / / / / / / / / / / / / / / / / / / //
// = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = //
2) E:\share\QlikView\Common Reference\Data\Compliant Events\QVDs
Compliant Events Feb 2015.qvd
Compliant Events Mar 2015.qvd
3) E:\share\QlikView\Common Reference\Data\Compliant Events\QVDs
Compliant Events.qvd
3) Code.....
? ? ? ?
Hi Mindy,
You may want to take a look at this blog post on picking up a folder full of CSV files:
http://www.quickintelligence.co.uk/convert-drop-folder-files-qvd/
To create the monthly QVDs I would assume you only want to do this for the current month (as there is no point recreating old months). There are also only a finite number of file types, so you can loop these. So you could end up with something like:
// Set the current period to variables
let vThisYear = Year(today());
let vThisMonth = right('0' & Month(today()), 2);
// Load the types of file we are dealing with
Temp_FileMasks:
LOAD FileMask INLINE [
FileMask
Compliant-Unix-Interactive_
Daily-Wintel-Interactive_
Wintel-RemoteInteractive_
];
// Loop through these file types
for iFileType = 0 to NoOfRows('Temp_FileMasks') - 1
// Grab the next mask to deal with
let vFileMask = peek('Temp_FileMasks', iFileType, 'Temp_FileMasks');
// Load all data for the current month to refresh the monthly file
TempMonthsData:
LOAD
*
FROM $(vFileMask)$(vThisMonth)-*-$(vThisYear)-*.qvd (qvd);
// Write this file away and clear the data down
STORE TempMonthsData INTO $(vFileMask)-Monthly-$(vThisYear)-$(vThisMonth).qvd (qvd);
DROP TABLE TempMonthsData;
// Create a master QVD from all monthly files
MasterQVD:
LOAD
*
FROM $(vFileMask)-Monthly-*.qvd (qvd);
STORE MasterQVD INTO $(vFIleMask)-MasterFile.qvd (qvd);
next
This is untested code, but hopefully it will point you in the right direction for achieving what you are after. The only extra thing you may have to consider is building for the previous month as well as the current (if files come in at the end or after the end of the month).
Hope it helps.
Steve
Thanks for the code Steve. I'll have to take time to learn and understand it all. This will probably work going forward on a monthly basis as you have the code written but right now I have to do this for a thousand files dating back to December of last year. And, of course, not all the file names are consistent...yet. That is why I am hoping to use an internal date field to get the bulk loaded.
Example of names:
NonCompliant CSVs folder:
Golden Host-Daily Report(DR)-Wintel-RemoteInteractive_04-02-2015
Golden Host-Daily Report(DR)-Wintel-RemoteInteractive_04-04-2015-15-02-20
Golden Host-Daily Report(DR)-Unix-Interactive_04-13-2015R
Golden Host-Daily Report(DR)-Unix-Interactive_04-13thru20-2015
Bypass_Report(DR)-Unix-Interactive_12-01-2014-12-07-2014-pt1
Bypass_Report(DR)-Unix-Interactive_12-08-2014-12-10-2014
Compliant CSVs folder:
TAMCompliant_April-2015_Full
TAMCompliant_March2015 - new DTG format
TAMCompliant02_May-2015
TAMCompliant25-May-2015_00_00
TAMCompliant25-May-2015_12_00
Hi Mindy,
It's going to be fiddly getting everything straightened out there, where you have date ranges and suffixes.
The routine you have to turn CSVs to QVDs will help you out.
Going back through prior months will be possible by creating a loop that goes back through prior months. The loop would look something like this:
let vMonthsBack = 8;
for iMonth = 0 to vMonthsBack
let vThisMonth = Date(AddMonths(today(), -iMonth), 'MM-YYYY');
// Create your merged files in here.
next
Do the files you are processing have date fields in the files as well? How many different file formats do you have, or are all file formats the same just with different file names?
- Steve
Hi Steve,
For the first part I actually found some really nice code. Yet, when I tried to move forward with what you provided me, I got the "...file not found..." message. Then I realized that it dealt with the date fields in the filename. What I need to do is replace the texts with the dates within the file names?
How do I go about replacing the text May-2015 to the date May-2015?
TAMCompliant01-May-2015_12_00.qvd
To get the dates:
Date(Date#(SubField('$(vBaseName)','-',2),'MMM'),'MMM') as FileMth,
Date(Date#(Left(SubField('$(vBaseName)','-',3),4),'YYYY'),'YYYY') as FileYr;
Step 1) ... good to go
TRACE ==== Auto load source .csv files into .qvd files with same name in different folder ====;
LET vSourcePath = 'E:\share\TAM\QlikView\Common Reference\Data\Compliant Events\';
FOR Each vFile in FileList ('$(vSourcePath)*.csv')
LET vBaseName = Mid(SubField('$(vFile)','\',-1), 1, Index(SubField('$(vFile)','\',-1),'.',-1)-1);
IF IsNull(FileTime('$(vSourcePath)\QVDs\$(vBaseName).qvd')) then
tmpTable:
LOAD
*,
1 as Row_Counter,
'Compliant' as Compliancy,
FileName() as Source_File
From
[$(vSourcePath)\$(vBaseName).csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
STORE tmpTable into [$(vSourcePath)\QVDs\$(vBaseName).qvd] (qvd);
DROP Table tmpTable;
END If
NEXT
LET vBaseName=;
LET vFile=;
LET vSourcePath=;
TRACE ==== End .csv to .qvd autoload ====;
Hi Mindy,
I'm not sure I follow your question quite.
What you want to do as a next step is to loop for each month, and merge all QVDs for that month into a single one. Is that correct?
For your folder of Compliant QVDs (as they will be out of the back of the first routine) you could do something like:
let vMonthsBack = 8;
for iMonth = 0 to vMonthsBack
let vThisMonth = Month(AddMonths(today(), -iMonth));
let vThisYear = Year(AddMonths(today(), -iMonth));
for each vFile in FileList('$(vSourcePath)\QVDs\TAM*$(vThisMonth)*$(vThisYear)*.qvd')
MonthlyFile:
LOAD
*
FROM [$(vFile)] (qvd);
next
STORE MonthlyFile INTO [$(vSourcePath)\QVDs\Monthly\TAMCompliant_Monthly_$(vThisMonth)$(vThisYear).qvd] (qvd);
DROP TABLE MonthlyFIle;
next
This will only work for some of your files though, as for others you have numeric months, so would need to provide a different file mask
Also, I'm not sure if having the * between the Month and Year in the FileList will work where you have May2015 in one of the filenames without a hyphen.
Hope that helps move you a step forward though.
Steve
Hi Steve,
I hope you don't mind me crashing this blog. To accumulate my historical data I have to store into a .qvw table correct? I.e. I cant append to an existing qvd file right?
lc
Hi Laura,
Of course I don't mind!
What I would suggest is that you take a read of the blog post that I put a link to in my initial comment. This should make things clear.
You can append historical data from one QVD into a new QVD that has both old and new rows. The way that you do this is to load both old and new data into a QVW file and then issue a STORE statement over the top of the existing QVD (or to a new one, if required).
It is a common practice to have separate 'QVD Generator' apps, that LOAD and STORE data and then DROP all tables - so that the app contains no data. The QVW files with the charts then load purely from QVD files.
Hope that helps,
Steve