Pseudo script with 3 different types of data sources

[#1]

Hello all,

I am submitting this pseudo script as a follow up to my original question about QlikView scripts and usage of multiple data sources. Please let me know if this makes sense overall; and also if there are better ways to do it.

Let's assume we have an existing QlikView script that pulls data from an Oracle database as follows:

ODBC CONNECT TO [my_oracle_database];

Tab1:
Load f1,f2,f3;
SQL
SELECT f1,f2,f3 FROM my_db_table;


Now, let's assume that we have additional data that need to be included in our report.
This additional data though is not in my Oracle database but rather some of it is in a MS Access (Northwind.mdb)
and some of it is in Excel file (draft.xls).

Per the QlikView Reference Manual, the script can be modified as follows to combine all 3 data sources:

// Point to my Oracle database first
ODBC CONNECT TO [my_oracle_database];
// All selects from here on will get data from oracle database my_oracle_database

Tab1:
Load f1,f2,f3;
SQL
SELECT f1,f2,f3 FROM my_db_table;

// Point to MS Access second
Connect to 'Nwind;DBQ=C:\ProgramFiles\MSOffice95\Access\Samples\Northwind.mdb' (UserID is sa, Password is admin);
// All selects from here on will get data from MS Access Northwind.mdb

Tab1:
// Using add to merge new selected records to the previous records in Tab1
add
Load f1,f2,f3;
SQL
SELECT f1,f2,f3 FROM my_db_table_in_ms_access_Northwindmdb;

Tab1:
// Point to MS Excel third
// Using add to merge new selected records to the previous records in Tab1
add
Load f1,f2,f3
FROM (biff, embedded labels, table is [SQL Results$]);

Thanks you for your review and help.

Yazid B.

[#2]

Hi ,

Sounds good to me.

That works that way.

Philippe

[#3]

Hello Yazid,

the script will work as expected. But you do not need the keyword "add" in the way you (mean to) use it. An "add" in front of a load-statement takes affect only(!) when you start a "Partial Reload" from Mainmenu/File/Partial Reload or with <ctrl> <shift> R. In this case two things will happen: a) the existing tables won't be truncated and new records will be added where "add" is specified. In your case using the script a second time with Partitial Load only the data from "my_db_table_in_ms_access_Northwindmdb" and "" will be appended (and therefore being twice in your table).

This means that QV takes care of sourcefield names (case sensitive!) and inserts and appends the records of your three sources into one table because all the fieldnames are the same. See Help for more information about the (automatically) concatenate load. This is a powerful but sometimes suprising feature.

HtH

Roland