Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Design approach Help

Hi,

I have a scenario here.

I am  loading 150 million records into Qlikview QVD file today around 6.00 A.M.

And I generate my reports.

Tomorrow again if I am running, I should not load all the 150 million records.

From today 6.00 A.M to 2mrw 6.00 A.M only the records that has been added or updated should be refreshed in the existing 150 million

records QVD

Instead loading all the 150 million records from the sql table

How can it be done??

3 Replies
swuehl
MVP
MVP

You can use an incremental load approach, as described in the reference manual, in some threads here in the forum or in the QV cookbook you can download from Rob's download page:

Downloads - Rob Wunderlich Qlikview Consulting

Nicole-Smith

Search for "QVD files and Incremental Load" in QV help.  It will give you the different ways that your data can be set up along with some examples.

Not applicable
Author

This is just a sample from code that I use for a very large table. Please note that the first line of code Let vLastLoadTime isn't needed except for the first time you run the code. Likewise the Concatenate block of code isn't used the first time.

Obviously you will need to substitute your actual tables, and fields and change the format of the date fields if you aren't using SQL Server. This is for SQL Server. CHR(39) is simply a single quote mark

// Let vLastLoadTime = chr(39) & '01/01/1900 00:00:00' & chr(39);

Let vLoadTime = chr(39) & Date(Now(),'M/D/YYYY h:mm:ss[.fff] TT') & chr(39);

Journal_Transactions:
LOAD "jnl_int_id",
"crp_int_id",
.... bunch of other fields

"row_sta_cd_JournalHeader",
"lst_mod_id",
"lst_mod_ts";

SQL SELECT *
FROM Journal_Transactions"
where lst_mod_ts >=
$(vLastLoadTime)
and lst_mod_ts <
$(vLoadTime);

Concatenate (Journal_Transactions)
Load *
FROM DataFiles_GenFin\Journal_Transactions.qvd (qvd)
where not exists (jnl_dtl_int_id);

Let vLastLoadTime = vLoadTime;

STORE Journal_Transactions into DataFiles_GenFin\Journal_Transactions.qvd (qvd);
DROP TABLE Journal_Transactions;