maybe there is anyone here, who can help me to create a script that loads several access tables into QlikView. My problem is as follows:
I have a MS Access database with several tables of the same structure (same number of cols, same col names, same data types,....). I could unite all tables into one big table but that won't help me in the long term, because the number of tables varies over time.
Instead I want QlikView to load all tables that have the same prefix in the name. E.g. the tables names could be "Cost_data_2015", "Cost_data_2016", "Cost_data_2017", ... and so on. Then I want to load all tables that start with "Cost-data" w/o adjusting the script everytime I get a new table.
I already tried out something like "LOAD * From Cost_data_*" but it did not work so far.
I suggest that you load all mentioned tables within a single table in Qlik. For it you may use a simple loop, like:
for i = 2017 to year(today()) sql select * from Cost_data_$(i); next
Beside this you may query the Access system-tables which contain the information which tables/fields exists and which properties they have. I don't remember the exact names and syntax but google will help you by it - and then you could loop over these information.
Another thought goes to the data-structure within the Access DB - why is there a table for each year? I think I would have tried to avoid such logic and merging it into a single table.
