Friday, May 21, 2010

Use cases for Generic Load

The Qlikview  "Generic Load" is not frequently used and is therefore sometimes forgotten. Generic Load has some interesting applications and can be a useful item in your script toolbag.

Generic Load  is the complement of "Crosstable Load". In a loose sense, you could say that a Crosstable Load creates a Generic table and  Generic Load creates a Cross table.

Consider this table which contains a separate row for each Phase of a Project.

Now suppose you want to flatten this table to a single row per Project. You have a variable number of Phases per Project. The resulting data model should look like this:

The model above can be accomplished with a two GENERIC LOADs against the ProjectTable

GENERIC LOAD Project, 'Start Phase ' & Phase, StartDate
RESIDENT ProjectTable;
GENERIC LOAD Project, 'End Phase ' & Phase, EndDate
RESIDENT ProjectTable;

Generic Load creates additional Qlikview tables. The additional tables cannot be avoided by combining a CONCATENATE or JOIN prefix. In the next example I'll offer a technique to consolidate the tables.

Here's another application of Generic. Consider this example table.

Suppose you want to generate flag fields for each of the possible order statuses? The flags could be created with a single Generic Load.

GENERIC LOAD Order, 'Status_' & Status, 1
RESIDENT OrdersTable;

The resulting data model now contains flags for each Order.

As mentioned previously, Generic Load creates additional tables. The table view after the above Generic Load is:

You can see Generic makes a new table for each new field it creates. That's fine if it doesn't cause synthetic keys or other problems. If you want to merge the Flag fields into the fact table (OrdersTable), you can do it after the Generic Load with a bit of code like this:

FOR i = 0 to NoOfTables()
  LOAD TableName($(i)) as Tablename AUTOGENERATE 1
  WHERE WildMatch(TableName($(i)), 'Flags.*');

FOR i = 1 to FieldValueCount('Tablename')
  LET vTable = FieldValue('Tablename', $(i));
  LEFT JOIN (OrdersTable) LOAD * RESIDENT $(vTable);
  DROP TABLE $(vTable);


Here's the table view after the Joins.

Generic load is not an everyday tool, but can prove useful in specific situations.

The qvw examples used in this post may be downloaded from here.

-Rob Wunderlich