topic Multiple MS Access Tables & QVDs in QlikView

Multiple MS Access Tables & QVDs

Mon, 23 May 2011 18:23:46 GMT

It is my understanding that, when selecting multiple tables from one MS Access mdb, a QVD must be created for each table. Is there a work around for that. Thanks for your help.

SQL SELECT `Absorption $ Effect`,
    `Absorption % Actual`,
    `Actual Units Hour`,
    `Daily Prod ID`,
    `Downtime Hours`,
    `Floor Changes Units`,
    `Product Number`,
    `Production Eff %`,
    `Sched Hours`,
    `Sched Units`,
    `Yield Eff %`
FROM `Detail - Efficiency`;
SQL SELECT `Absorption %`,
    Batches,
    `C/O Hours`,
    `C/O Hours per occurance`,
    `Change overs per day`,
    `Cost per unit`,
    Crewing,
    `Daily Prod ID`,
    DoughScrap,
    Hours,
    `Labor cost per hour`,
    `Prod Number`,
    `Rate/Hour`,
    `Scaling Weight`,
    SpongeScrap,
    `Target eff Labor`,
    `Target eff Machine`,
    `Target eff Yield`,
    `Units Prod`,
    `Yield per Dough`
FROM `Detail - Prod Main`;

Multiple MS Access Tables & QVDs

kaushiknsolanki — Mon, 23 May 2011 18:36:50 GMT

Hi,

The best practise is to generate qvd for each and every table,

But it also depends up on your data model.

For example if you always need table A left joined with table B, then you can create a qvd which will have the data from A joined B.

Regards,

Kaushik Solanki

Re: Multiple MS Access Tables & QVDs

yasus — Mon, 23 May 2011 18:49:53 GMT

Hi sumski,

with reference to Kaushik's comment a common best practise is to store all loaded/created table in qvd-format by using the following code at the very end of your script for expample:

FOR i = 1 to NoOfTables()
  LET vTableName = TableName($(i)-1);
  LET vOutfile = '$(vTableName).qvd';
  STORE [$(vTableName)] INTO [$(vOutfile)] (qvd);
NEXT i