The Generic Load

[hic]

There are a number of prefixes in QlikView, that help you load and transform data. One of them is the Generic prefix.

[hic]

Whenever you have a generic database, the Generic prefix can be used to transform the data and create the desired fields. A generic database is basically a table where the second last column is an arbitrary attribute and the very last is the value of the attribute. In the input table below you have a three-column generic database.

But if you want to analyze this data, it is much easier to have all attributes in separate fields so that you can make the appropriate selections. It is not very practical to have one single field for all attribute values, since you may want to make selections using different attributes at the same time.

Enter the Generic prefix.

It converts the data to a structure where each attribute is placed in a field of its own. Another way to express it is to say that it takes field values and converts these to field names. If you compare it to the Crosstable prefix, you will find that they in principle are each other’s inverses.

The syntax is

   Generic Load Key, Attribute, Value From … ;

There are however a couple of things worth noting:

• Usually the input data has three columns: one qualifier field (Key in the above example), an Attribute and a Value. But you may also have several qualifying fields. If you have four or more columns, all columns except the two last will be treated as qualifying fields.
• The Generic prefix will create several tables; one table per attribute. This is normally not a problem. Rather, it is an advantage: It is the least memory-consuming way to store data if you have many attributes.

If you have more than one key, this means that you will get a composite key – a synthetic key – in the data model:

Although it looks ugly, this synthetic key is completely harmless. But it may still be a good idea to replace it with a manually created concatenated key:

   Autonumber(Key1 & '|' & Key2 & '|' & Key3) as Key,

Finally, I have seen many examples on QlikCommunity where a For-Next loop is used to join together all tables created by the Generic prefix, e.g.:

   Set vListOfTables = ;

   For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

      If Subfield(vTableName,'.',1)='GenericLabel' Then

         Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;

      End If

   Next vTableNo

   CombinedGenericTable:

   Load distinct Key From GenericDB;

   For each vTableName in $(vListOfTables)

      Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];

      Drop Table [$(vTableName)];

   Next vTableName

The result is one big table that contains all attributes; a table that often is sparse (containing many NULL values) and much larger than the initial tables. And no performance has been gained… So I can only say:

You should not do this - unless you have a specific reason to.

The Generic prefix creates a set of tables that store the data in an optimal way. In most cases you should not change this. I realize, however, that there are cases where you want to transform the data further and need the data in one, unified table. Then the above scriptlet can be used.

HIC

[#3]

Henric Cronström

Thank you for your reply. Your solution worked perfectly.

Keep up with the excellent support and knowledge sharing via the blog.

Regards, Andrea

[Anonymous]

Thanks for sharing .

[#5]

Really very useful

great work sir

Thanks for sharing

[brindlogcool]

It is very good post.

Generic Load works well when there is a key value. When we dont have the key then we cant use the generic load.

[rwunderlich]

You don't have to have a Key, although in most cases you would. If you don't have a key you can use a dummy or generated value for the first field. eg

Generic Load 0 as Key, Attribute, Value...

[brindlogcool]

Thanks Rob. I found any way of converting the rows in to columns through Peek Function and Concat.Also no for loops and Joins

In My Case i wasn't having keys.

[ankitbisht01]

very nice blog thank you

[qlikviewwizard]

Nice document. Thank for sharing.

[johnw]

I don't think you're right about "much larger than the initial tables". I mean yes, conceptually, of COURSE it's much larger if you think of it as one big table with most of the cells null. But I'm pretty sure that's not how QlikView actually stores data internally. (Edit: Mind you, you'd probably know far better than me how the data is stored internally.)

I have one of these loop and joins working on a table with 16,225,873 rows in the original table and 561 fields in the resulting table, which I would expect to be fairly sparse, with maybe 80-90% of cells null. I did a little testing of the size of the original, of the generic before joining, and of the joined result.

                         Original  Generic    Joined
QVD KB             97,521         NA    52,440
QVW KB            50,205    38,008   13,143
In Memory MB     116.0      137.5       74.1

So for at least my example, the joined version makes more efficient use of both QVW size and in-memory size than the generic version with all the individual tables. This isn't meant to suggest that joined is in general better, but I believe it at least demonstrates that the picture is not nearly so clear as one might at first think.

And I'll be honest - I expected the generic and joined sizes to be virtually the same based on my vague understanding of how QlikView stores data. I was surprised that the joined version was so much smaller. I have no explanation right now. I would have thought that generic would, just as you said, "store the data in an optimal way", and that the joined version would be negligibly larger. Seems we were both wrong for at least my example.

All that said, I agree that you should have a REASON to do the joining. It appears that space might be a rather surprising reason. An easier-to-view structure when you pull up the table diagram might be a reason. And in my case, the need to store the resulting data as a QVD was the primary reason. But I would in general definitely start with the unjoined generic and see if it suits my needs. No reason to increase my script complexity and load times unless I have a compelling reason to do so.

[hic]

Of course it depends on the data which result you get: The sparsity of the joined table (number of NULLs) and the cardinality of the fields.

The symbol tables will be the same in both cases. (See  Symbol Tables and Bit-Stuffed Pointers)

In the generic version, there will be one data table per attribute, and these will have two columns and just the right number of records. Keys without this specific attribute will not be stored.

In the joined version there will be only one data table, with one column per field and potentially a very large number of records. Every key value must have a record. Empty cells will have NULL, and since also NULL has an index, this will use memory. NULL uses the same number of bits as the other values in the column.

Hence, the drawback of the generic version is that the key field is repeated in every data table. And the drawback of the joined version is the large number of records of the data table. The question boils down to which of the two is more costly memorywise. If most attributes are used by most key values, then the joined version obviously is more compact. But if you have a very large number of attributes, and each key value only uses a few of them, the joined version is (or ought to be) is a potential memory problem. 

But I must admit that I am a little surprised that the joined version is that much smaller...

HIC