Scripting

Use cases for Generic Load

7 Comments
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 aCrosstable 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.

Flags:
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 = NoOfTables()-1 to 0 STEP -1 
  LET vTable=TableName($(i)); 
  IF WildMatch('$(vTable)', 'Flags.*') THEN 
    LEFT JOIN (OrdersTable) LOAD * RESIDENT    [$(vTable)]; 
    DROP TABLE  [$(vTable)]; 
  ENDIF 
NEXT i


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
Share

7 thoughts on “Use cases for Generic Load”

  1. Rob, this info was exactly what I was looking for…last week! 🙂

    After trying a generic load I ended up hard-coding the names of the values I was using (comparable to hard-coding the status fields in your example) and then I joined the needed columns.

    I didn’t like my solution but it works for now. Your solution is much better. Hopefully I will have the chance go back and implement your solution before my hard-coded fields come back to bite me.

    Thanks for posting!

  2. Hi ROB , This is great code , especially you are able to create status variable using generic load. which i try to ask 2 week ago , i like generic load , because it make me easy maint my code , when i have similiar data structure for more then 1 company. but then after manage create generic load , i have problem in create autonumber variable to link sales table with payment table. not sure when you free are you able to help to share with us ?

    http://community.qlikview.com/forums/p/29739/114333.aspx#114333

    Paul

  3. Very good solution Rob. I am working on a similar application ( visualizing time series data from projects ), and I wondered if there is any possibility in Qlikview to use horizontal bargraphs to display dates like in a typical project Gantt chart ( to easily display past due tasks, coming due tasks, completed tasks …etc ). It would be great if the background of the plot could have different color zones to dinamically allocate past due tasks, coming due tasks and future tasks ( keeping a reference line for today´s date ). I am not sure if it could be possible..but it would be a great help. Any experience in a case like this ? any suggestions ?
    Thanks a lot
    quiquehm@gmail.com

  4. @quiquehm,

    Search the QlikCommunity forum for “Gannt Chart” and you’ll find some examples. I recommend you use google to do the search — the forum search does not always return all results. In google:

    gannt chart site:qlikview.com

    -Rob

  6. This was great, thanks. We were dealing with hundreds of tables that were created after generic load, and good to re-join them. We had errors but figured out that spaces and other special characters in the new tables were the culprit. PURGECHAR(Orders, ‘ ‘&’/’&’-‘&'”‘) as Orders, was basically how we fixed it. (Our field was attributenames, and values were everything from name to gender to birthdate and various special codes.) Anyway, thanks for addressing cleanup of generic loaded table mess! -Steve

  7. Awesome!
    This is what exactly I was looking for.I could resolve one of the complex problem with this! Thanks, Aji Paul

Comments are closed.