Loading a 1% Random Sample

I needed to load a one percent random sample of a large file last week. I’m sure others have figured out this method but this was the first time I coded it like this:
MYSAMPLE:
Load * from MYFILE.qvd (qvd)
Where Rand()<=0.01;

The Rand function returns a random value between 0 and 1. In the load statement above it would only be true 1% of the time (approximately) -- that would load only 1% of the rows. If it was coded as Where Rand()<=0.20 then it would be a 20% random sample, etc.

This method can be used with almost any Load statement even one of the steps in a preceding load. 
Note that we're talking about random numbers here so the total number of rows loaded using this technique will not be exactly 1% of the total rows in the file, although it will be close especially when the total number of rows in the file is very large. Be aware too that the rows that get loaded will not be an evenly distributed sample of rows although it will be close.

  ★★★

Reducing Report Size

In the previous posting I showed how a table in an existing report could be changed to make the table smaller (Click here to read it). In a comment to that posting, Rob Wunderlich correctly points out that a much simpler way to make the report smaller is to select the data you are interested in retaining and then, from the menu, select File->Reduce Data->Keep Possible Values. Then save the report under a different name in case you ever want to refer to the original. Thanks for the comment, Rob. That technique is great for permanently removing data from a report for any reason, not just to make the report smaller (e.g. removing old data, sensitive, irrelevant, unneeded, or erroneous data).

The method shown in the previous posting for changing a table in an existing report is still useful for changing field values, adding or joining new fields, etc. Someday I'll have to edit that posting to a more relevant example.

Change a Table Without Reloading Report

Last month I wrote a posting about interesting things you can do in the loadscript with a Partial Reload. Here's another:

I had a large report that we needed as an example for a presentation but it was just too large. The original database it loaded from is no longer available, so I needed a way to make the report smaller without reloading it. It had one large shipment data table in it and I decided to change the the table to only include two shipping location codes which would make it much smaller.

See the picture above (click it for a better view). I inserted this code at the top of the loadscript:

/* Example loadscript fragment to adjust a table */
/* Put this code at the top of the loadscript and */
/* then run a Partial Reload. */
/* Remove this fragment after using it. */

//Rename the table we want to change
RENAME TABLE HIST TO HISTX;

//Rebuild table with a WHERE clause
HIST:
ADD NOCONCATENATE LOAD * RESIDENT HISTX
WHERE (LOC='001099') or (LOC='011098');

// Drop the original table
DROP TABLE HISTX;

//so that we don't fall into the regular loadscript
EXIT SCRIPT;

Then, I saved the report file under a different name (just in case) and ran a Partial Reload. The Partial Reload executed the commands at the top of the loadscript without removing all of the other data and made the report file a lot smaller and the memory or RAM requirement for the report smaller too. Unless you know that you'll never need to reload the report again you should remove the loadscript lines you added at the top.

Don't forget the ADD or the NOCONCATENATE keywords or you'll find that the table you wanted to rebuild is missing after you run the Partial Reload.

Field names and compressing data

One of the cool things about QlikView is the way it can store enormous amounts of data in the report file. It can load many millions of rows of data into a report file that might only be one or two MB in size. It compresses data so well that I seldom zip a report file before sharing it since the data is stored so densely in the QV report that zipping the file doesn't give much of a savings. If you are working with loading very large tables into a report be aware that part of the QV strategy for compressing data depends on not actually storing duplicate values in a field. If the report loads sales order data, for example, and the data contains customer name then there may be many duplicate values in the data for any customer who has ordered many times. QV will only store the customer name once and then keep track of where that customer name is used again as subsequent rows are loaded.

This mechanism means that if you load two different tables from your database that contain customer name into two different QV tables and if the field name is the same for both QV tables then each customer name value is only stored once. But, if you give the field a different name in each QV table then the program cannot know that it is the same data and it must treat each field separately and many customer name values will be stored twice. This is really only a consideration when working with enormous tables where the sheer size of the tables is affecting memory utilization of the report or the size of the report file.

Remember that QV relates or joins the tables together based on the fields they have in common. Two QV tables that both contain the field Customer_Name will be related based on that field. So, sometimes making the field names in the tables the same to be more efficient with memory utilization will not be possible if it causes two tables to be joined when they shouldn’t (the joining of tables also consumes memory space). And the field names should only be the same if the field data is truly the same. Customer number from your company’s database is not the same as customer number from another company’s database. But, customer number from your company’s 2006 sales data is probably the same as customer number from the 2007 sales data.