automatic load loop from multiple files by year

Anonymous — Fri, 02 Aug 2013 12:46:22 GMT

Hello,

I am trying to create an automatic load script, based on data that is stored in different Access databases by year (each year in a different database).

I only want to load the current year and the previous two. The year is indicated in the filename, so I would like the script to use the variable name in both the loop variable (For i=...) and the filename (PathName+i ).

Here is how far I got so far, of course not working correctly.

If you have any idea on how to proceed, I would really appreciate any help.

Let vCurrentYear = Year(Today());

Sub LoadData

For i = (Num($(vCurrentYear)–2)) To Num($(vCurrentYear))

OLEDB CONNECT32 TO [Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=$(vServerPathName)$(i)& issues report .mdb;Mode=Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Database Password="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False];

Issues:
LOAD

Field1,

Field2,

Field3 ;
SQL SELECT *
FROM Issues;

Next i

STORE Issues into $(vServerPathName)Issues.qvd;
DROP TABLE Isues;

End Sub;

Call LoadData;

topic automatic load loop from multiple files by year in QlikView

automatic load loop from multiple files by year