A Little More Optimized Load

This is a continuation of last week’s blog topic (search on "QlikView Maven Optimized QVD Load With a Where Clause and Dates"). 
I had a load statement in a loadscript loading from a qvd file that (simplifying a bit) looked like this:

INVOICE_DATA:   
Load CUST_NO,
ApplyMap('custprimap',CUST_NO) as CUST_PRIORITY,
INVOICE_NO,
INVOICE_AMT
from invoice_data.qvd (qvd) 
where exists(INV_DATE); 

It was written using a mapping table feature for customer data to avoid a left join load for performance reasons. But, the load from the qvd file wasn’t an optimized load because of the ApplyMap function. The table contained many millions of rows and using an optimized load would save quite a bit of time. 
I was able to get an optimized load and still have the ApplyMap functionality by recoding that portion of the loadscript to also use a Join and a super-fast load from FieldValue like this: 

INVOICE_DATA:   
Load CUST_NO, //optimized load
INVOICE_NO,
INVOICE_AMT
from invoice_data.qvd (qvd) 
where exists(INV_DATE);

Join (INVOICE_DATA) load //preceding load
CUST_NO,
ApplyMap('custprimap',CUST_NO) as CUST_PRIORITY; 
Load FieldValue(CUST_NO,IterNo()) as CUST_NO
Autogenerate(1) while not isnull(FieldValue(CUST_NO,IterNo()));



______________ _________________ ________________

Note to Santhosh:  You can add special comments to a chart expression that will make it easier to find specific expressions with the Settings->Expression Overview, Find feature. For example, you might use an expression like this:
sum(CONTRCT_S1 * CNTRY_ADJ) //per NATO procurement 1987.36.c
and then it would be easier to find the expressions that need to be changed when procurement rules change by doing an Expression Overview Find on "NATO procurement".
Our otherwise well-intentioned friends who believe all chart expressions should be contained within variables cannot easily use this suggestion.

  ★★★

Optimized QVD Load With a Where Clause and Dates

If you are working with large quantities of data and especially if you are working with large data sets of historical data where you add a portion of new data to the historical data each week (an incremental load), then you will want to be working with qvd files in your loadscript. And if you are working with qvd files then you certainly want to load data from the qvd files with an optimized load. The optimized load is many times faster than loading the same data with an unoptimized load. 

A common problem is that adding a where clause to the load statement will cause the load to be unoptimized. Some types of where clauses though, can be used with an optimized load. A simple where exists type of where clause, for example, can usually be used with an optimized load.

Every incremental load process is a little different but if your process involves dates then this idea may help. This idea is for situations where you want to load data from a qvd file and you want your where clause to refer to a date within the data. Here’s an example:

Imagine that you have a date like 01/01/2014 in a variable named start_dt and another date like 12/31/2015 in a variable named end_dt and you want to load invoice data where the invoice date, inv_date, is between start_dt and end_dt. First, build a little table of all possible dates between start_dt and end_dt. Use this in the loadscript before loading any other data containing a INV_DATE field:

/* Build table of dates just for the "where exists" clause */
TMP_DATE_TABLE:
Load date(date('$(start_dt)')+IterNo()-1) as INV_DATE 
AutoGenerate(1)
While date(date('$(start_dt)')+IterNo()-1) <= date('$(end_dt)'); 

That code can build a large table of dates in your loadscript in just a few seconds. Now, you can code the load from qvd like this:

INVOICE_DATA:   //this is an optimized load
Load * from invoice_data.qvd (qvd) 
where exists(INV_DATE);
Drop table TMP_DATE_TABLE; //no longer needed

That table of dates can be varied as needed for your specific application. For example, using (IterNo()-1)*7 could give you a set of week start dates. Use whatever method makes sense to create a list of the dates that you would want to load if they exist within the qvd file data. Remember, the basic idea here is to build a table of values you would like to load from the qvd file. It is not limited to dates and not all of the values in your temp table have to actually match something in the qvd file.

____________ _________________ _________________

Note to Santhosh:  If you need to quickly find the maximum or latest invoice date from the qvd file in order to use it with date arithmetic then you can get it in a variable by doing something like this in your loadscript before loading any other tables with the INV_DATE field:

TMP_INV_DATE:  //this is an optimized load
Load INV_DATE FROM invoice_data.qvd (qvd);
TMP_MAXINV_DATE:
Load Max(INV_DATE) as maxinvdate;  //preceding load
Load FieldValue('INV_DATE',IterNo()) as INV_DATE
AutoGenerate(1)
While not Isnull(FieldValue('INV_DATE',IterNo()));
Let var_maxinvdate = Peek('maxinvdate',-1,'TMP_MAXINV_DATE');
Drop tables TMP_INV_DATE, TMP_MAXINV_DATE; //not needed

★★★

Export All Data From All QV Tables to Text Files

Last week one of our clients asked me to provide a method that they could use to export all of the data from within a QlikView document. They had hundreds of archived documents and they wanted to be able to export the data and use it in another application.

“Sure, No problem. I’ll email you some instructions.” I sent them instructions for exporting all of the data from all QlikView tables within a document into tab-delimited text files. This is what I sent to them:

1. Create a new folder on your laptop or server. The computer where you do this must have QlikView installed.

2. Copy the QlikView .qvw document file into your folder. This will make sure you don't accidently update the original document.

3. Double-click on the .qvw file to start up QlikView.

4. From the menu, select File->Edit Script. This opens up the Loadscript Edit Window.

5. At the very top of the loadscript, before the first line, copy and paste in these lines:

/* Export all QlikView tables to tab-delimited files */
if IsPartialReload() then
for ix= 0 to 100
let tn=trim(tablename($(ix)));
if len('$(tn)')<1 then
exit script;
end if
Store "$(tn)" into "EXPORTED_QV_$(tn).TXT" (txt, delimiter is "\t");
next ix
end if
exit script;
//
6. Close the Loadscript Edit Window by clicking the OK button in the lower right corner of the window.

7. From the menu, select File->Partial Reload

8. You don't need to save the document, but if you do save it, do not copy it back to the original location.

If everything works correctly, each of the QlikView tables will be written out as tab-delimited text files into the folder. For example, if the QlikView document contains a QlikView table named HISTCUST then it would write out a text file named, EXPORTED_QV_HISTCUST.txt. The first record will contain the field names.

Note the syntax for specifying the delimiter character. If your Store command simply includes (txt) without specifying the delimiter you will get comma-delimited text files. Use the file specification, (qvd), to get QlikView QVD files.

I would've preferred a more elegant piece of code using a Do While loop but I had trouble getting the test to run correctly and I had limited time. The code above should work fine for documents with under 100 QlikView tables.

* * *

Loading All of the Files from a Folder

It's pretty easy to load all of the files from a folder into QlikView. For example, if you have a number of Excel spreadsheets in the f:\Finance\monthly_summaries folder and each spreadsheet contains a tab named EOM_SHEET and the column names on that tab are consistent in each spreadsheet file then these lines in your loadscript will load all of the spreadsheets into your QlikView report:

Directory f:\Finance\monthly_summaries;
For each ExcelFile in filelist ('*.xls')
EOM_HISTORY:
Load * From $(ExcelFile) (biff, embedded labels, table is [EOM_SHEET$]);
Next ExcelFile;

Note that this looping through the files of the folder will only use filenames that end with a .xls extension. So, any other documentation or data files in the folder will be ignored. The spreadsheets can contain other worksheet tabs too -- only the worksheet tab named EOM_SHEET will be loaded into QlikView. Hundreds of spreadsheet files can be loaded fairly quickly this way. As always with QlikView object names spelling counts and it's case-sensitive, e.g. EOM_SHEET is not the same thing as EOM_Sheet. If you're worried that not all of the spreadsheets use the same column headings then it might be best to code each column heading as a specific field name in the Load statement. That will make QlikView call it an error if the column headings in any of the spreadsheets don't match the expected field names. The error is usually better than loading inconsistent data. The Directory statement and Load statement can also make use of variables to make them more flexible to handle folder and file naming conventions. The load statement, of course, can be made to fit most situations with Where clauses and Group by options.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
In a comment, Guido correctly pointed out that this syntax achieves the same result and it's simpler:
EOM_HISTORY:
Load * From f:\Finance\monthly_summaries\*.xls (biff, embedded labels, table is [EOM_SHEET$]);

Check out the other comments to this posting.