Easy incremental load

I was demoing a quick incremental load script earlier today so I thought I would share it here.

To generate the test data, I used an echo command at the Command Prompt to generate data using the system time:

echo %DATE% %TIME% %RANDOM% >> Data.txt

Every time I run this command it writes a new line to the text file with the current system date and time and a random value.  Perfect for demoing an easy incremental load.

Here is the code:

// vLastLoadTime holds the last date of loading
// If it is not set, then set it to 1,000 days ago
If Len('$(vLastLoadTime)')=0 Then 
Let vLastLoadTime=Num(Now()-1000);
End If 

// vLoadTime holds the current run time
Let vLoadTime=Num(Now());

// Load the data from the data source
// between the two dates
Data:
LOAD @1:23 as DateTime, 
     @24:n as Value
FROM
[..\Data Files\TXTs\Data.txt]
(fix, codepage is 1252)
Where @1:23>$(vLastLoadTime)
And @1:23<=$(vLoadTime);

// If there were any rows,
// concatenate the QVD rows and re-Store
If NoOfRows('Data') > 0 Then

// Get the length of the QVD file
Let vFileLen=FileSize('Data.qvd');

// If the file exists, load it
if Len('$(vFileLen)')>0 Then

Concatenate (Data)
Load
DateTime,
Value
From [Data.qvd] (qvd);

End if

// Store the full table back to QVD
Store Data into [Data.qvd];

// We can drop the table
Drop Table Data;

End if

Let vLastLoadTime=vLoadTime;

// Load the data from the QVD
Data:
Load
DateTime,
Value
From [Data.qvd] (qvd);

Of course, this is just one type of incremental load that we can do using QVDs.  There is an excellent article in the QlikView help file that describes other scenarios.  It is worth reading.

---

Stephen Redmond is author of Mastering QlikView, QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond