Hi Its possible lo load data from multiple data sources. In real time we do does the same thing.
Here are different types of load for reference.
1. Laod from file:
You can load your data from excel/msdb/txt etc files or by creating ODBC and connecting to your database directly.
e.g.
LOAD BudgetYear,
Country,
Budget
FROM
(biff, embedded labels, table is Data$);
2. Inline Load:
You can load data from file or can define the data within Qlikview and load (Inline) from there. The inline data can be defined in the Inline Data Wizard as: Insert > Load Data > Inline Data.
e.g.
LOAD * INLINE [
Display as
Dollars
Percentage
];
3. Resident Load:
You can load data intoa Qlikview table. Then you can use that table as a resident table and data from that table with transfomation and calculation in resident load.
e.g.
emp1:
Select Name,DOJ,Salary,Bonus from employee;
Load Name,month(DOJ),Salary+Bonus as 'Total Payout' resident emp1;
You can also load from an existing field or a succeeding table.
4. Incremental Load (Differential/Delta Load):
Suppose your database data volume is big and you do not want to load the whole dataset everyday. Rather you want to load only the new/changed records for optimization. Incremental load is th solution then.
In this case, the typical process is to load the new data from database, load the old data from QVD and combine into a new QVD (repeated for each table).
The implementation can be done in the following way (think about SCD)
i. Append only - tracked by number of records. The number of records previously read is tracked and only the records from the last recodr till EOF is appended
ii. Insert Only - I - (No Updt/Del) - The records inserted after the last execution of the script, are added. This requires an SCD2 like effective data field.
iii. Insert and Update - IU - (No Del) - Records inserted or updated after last script execution are taken care of. Needs an effective date and PK field.
iv. Insert Update and Delete - IUD - This is basically a full fledged sync with the database.
5. Binary Load:
Binary load is used to share the entire dtamodel between 2 QVWs. Basically, the datamodel of one QVW (Q1) is copied from RAM to disk in 0 and 1 form, for another QVW (Q2). Thus Q2 inherits entire data of Q1.
e.g. if you have a base QVW where the common metrices are designed and you want to enhance this to build more business specific dashboards, Binary load is a good option.
Also, if you want to do incremental load, the loading of high volume historical data can be done by binary mothod, to utilize its speed.
Point to be noted here is: Binary has tobe the first statement of the script.
e.g.
Binary order.qvw;
Binary c:\order.qvw;
6. Add load:
Simply put, this statement blindly appends data from one table to the data of another table, having similar signature, during partial reload. It does not check for any duplicate. Hence, ADD LOAD or ADD SELECT is usually follwed by distinct or a proper where clause.
e.g.
LOAD OrderID, OrderAmt from Order_May.csv;
ADD LOAD OrderID, OrderAmt from Order_June.csv;
This will simply concate data from Order_June to Order_May. But OrderID might be duplicated. Hence, this statement can be properly shaped to remove duplicate data as:
LOAD OrderID, OrderAmt from Order_May.csv;
ADD LOAD OrderID, OrderAmt from Order_June.csv Where Not Exists(OrderID);
7. Buffer load:
With the BUFFER prefix, QVD files are created and maintained autmatically (QVDs cache or buffer the result of the statement). This is handy while doing incremenal load.
The QVD name is an internal one; 160 bit hex hash name comprising the entire the followingload/select statement. It is stored in the location as set in the User Preferences > Locations.
e.g.
Buffer without option:
buffer select * from Table1;
Internally a QVD file is created and the content of Table1 is internally stored in the QVD. This would be used indefinitely untill some other method is used.
buffer (incremental) load * from MyLog.log;
Same as incremental load. It is a typical solution for log files (text). Not for DB files.
buffer (stale after 7 days) select * from Table1;
It overwrites the current QVD and make a full load after 7(n) days. It can be used with DB tables. This is the timestamp till the QVD would be used as suorce. After this it would be a full reload and regeneration of the QVD (?).
