topic Re: Design approach Help in QlikView

Design approach Help

Fri, 13 Dec 2013 13:37:22 GMT

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??

Re: Design approach Help

swuehl — Fri, 13 Dec 2013 13:49:13 GMT

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

Re: Design approach Help

Nicole-Smith — Fri, 13 Dec 2013 13:54:29 GMT

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.

Re: Design approach Help

Fri, 13 Dec 2013 20:31:01 GMT

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;