Load a Table With All of the Values for a Field

An idea for developers of large reports--

Sometimes in the loadscript you need to build a table that contains all of the previously loaded values from the tables that have already been loaded. This situation comes up when a concatenated key has been used in the tables. For example, you might have a report that loads data into several tables from different sources but you’ve loaded each of the tables so that it has a key field comprised of product_code, customer_number, ship_location, and ship_date; all concatenated together but separated with an underscore character. This technique is sometimes used in large reports in order to reduce memory requirements by minimizing the number of synthetic keys that QlikView must create. Each of the large tables shares only one field, the concatenated key field, instead of sharing multiple fields and causing QlikView to build the synthetic keys.

At the bottom of the loadscript you must collect all of the concatenated key values and explode them into the individual fields so that QlikView can build the associations. If the concatenated key from our example is named CCKEY then code like this would load all of the existing CCKEY values:

ALL_CCKEY_TABLE:
Load
FieldValue('CCKEY',IterNo()) as CCKEY
AutoGenerate(1)
While not Isnull(FieldValue('CCKEY',IterNo()));

That will create a table of all CCKEY values. Now add the part that splits out the fields based on the underscore separator character:

Left join load
CCKEY,
subfield(CCKEY,'_',1) as product_code,
subfield(CCKEY,'_',2) as customer_number,
subfield(CCKEY,'_',3) as ship_location,
subfield(CCKEY,'_',4) as ship_date
Resident ALL_CCKEY_TABLE;

A table like this loads very fast since all of the data is already available in memory.