Discussion Board for collaboration on QlikView Scripting.
Types of load can be explained frm various point of views.Some apply while loading data from database and some apply while restructuring your data model within qlikview.
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.
(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.
LOAD * INLINE [
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.
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.
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.
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.
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 (?).
N.B. This is my first post. Please correct me if required.
I'm really interested by your post, and indeed, your advice.
I have a database to update every week (and ideally every day) and I have approximately 250000 entries to load via an ODBC driver.
So the idea would be to do an incremental insert only load.
Do you know how i could do that ?
Thank you in advance for you advices.
Sebastien, Incremental load is the best approach to save reload time. Please try to the sample logic mentioned below.
SQL SELECT PrimaryKey, Field1, Field2 FROM Table_X
WHERE ModificationTime >= #$(LastExecTime)#
AND ModificationTime < #$(BeginningThisExecTime)#;
WHERE NOT EXISTS (PrimaryKey,PrimaryKey);
STORE HistoryData INTO $(SourceFilePath)History.qvd;
Thank you for the answer.
So, I understand the approach. Now I will have to implement it which is not the same story…
Envoyé : mardi 14 août 2012 18:51
À : sbruckert
Re: Types of loads in Qlikview
created by Manoj Kumar Varatharajan <http://community.qlik.com/people/manojkvrajan> in Development (QlikView Desktop) - View the full discussion <http://community.qlik.com/message/248392#248392>
Buffer load.. intresting explanation.
But same thing [ Incrementa load ] can be achieved using normal scripting [ concatenate, where not exists logic ].
Can you say some more specific use case ?
Even automatic storing of qvd in user preference path, is fine. But still it can be written in script using store command.
So any other particular usage of it ?
One more type of load missed in the explanation .
The preceding LOAD feature allows you to load a table in one pass, but still define several successive transformations.
LOAD ..., ReferenceDate, Age( ReferenceDate, BirthDate ) as Age
LOAD *, Date( FromDate + IterNo() – 1 ) as ReferenceDate Resident Policies While IterNo() <= ToDate - FromDate + 1