Saving Space by Nulling Zeros

If reducing the memory requirement of your QlikView document is important, this tip might help shave a few bytes that could add up to a significant performance gain.

There are several documents on performance improvement that will suggest that you should shed fields that do not contain data that will be used.  My tip is to look at fields that has data that you do use and shed the data in those that you won't!

One example would be a numeric field that may contain a lot of zeros.  If the only use of this field is in a sum, then there is no different in that zero being there or not.  The only difference would be in a count or avg.  Therefore, if you have a lot of zeros in a field that will only be summed, consider transforming them to null() in your load.

   ...
   if(Field1=0,Null(),Field1) as Field1,
   ...

If you need to use that field in a horizontal calculation, you can use the Alt function:

   Alt(Field1, 0) + Alt(Field2, 0)

Another type field that we are often told to look at getting rid of is key fields.  They are often highly unique so will take up the most space in the dataset.  It is often straightforward to remove them, but what if they are needed?  Perhaps as a key link in a 1:M relationship.

For example, say you had an Account table with a key field called AccountID.  There is also an AccountHistory table which associates on AccountID.  You can't readily join the two tables so you need to keep the key field.  However, if there is not an entry for every Account in the AccountHistory table, you don't need to keep the value for that particular AccountID in the field!  Null it!

I might create a mapping table:

   KeyMap:
   Mapping Load Distinct
      AccountID, AccountID
   Resident AccountHistory;

Then, in my Account table load:

   ...
   ApplyMap('KeyMap', AccountID, Null()) As AccountID,
   ...

Now, this is not going to be significant over a million records.  However, over a hundred million, you will see a difference.

---

Stephen Redmond is CTO of CapricornVentis a QlikView Elite Partner