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

[kalyandg]

hi HIC,

very nice

Best Regards,

Kalyan.D

[#4]

Hey Henrich,

Thanks, this is a very good overview of a feature that I at first didn't see any use for. But now I can see that I underestimated this. I have a similar case with assets and attributes in part of our inventory. Until now I converted the attributes I used in the analysis in a more cumbersome way (not that there were many).

However, I seem to have over 1200 distinct attributes. With in total about 1.3 M values. Is there a limit to how many tables can be created in QlikView? The table view won't look that good :-).

Off course, if I would make use of this, I would first filter out the attributes that I'm not interested in (then translate the attribute names to something useful as label) and then do this generic load.

[hic]

That is indeed a lot of data... but QlikView should be able to handle it anyway.

One possibility is to make a hybrid solution: Load the 3-column generic database as it is (no transformation) and in addition make a generic load with an appropriate filter that loads e.g. the 10 most important attributes. The tables will link on the key field and you can make searches both in the transformed and the raw data.

HIC

[#6]

Thanks, that's what I thought. The always annoying thing is that I'll need to deal with translations also. I use extra columns for this so my generic table would look more like:

- ID asset

- Attribute

- Attribute (nl)

- Attribute (fr)

- Value

- Value (nl)

- Value (fr)

I'll have to write a script that joins the translations into these new tables.

[hic]

A comment on the script in the post: It makes a Left Join in each loop - something that could be very memory consuming if you have many attributes. An alternative is to use a Group by combined with Subfield. The For-Next loop creates variables that contain field lists used in the Load. It should perform better if you have many attributes, but I haven't verified this. But go ahead and try it:

AttributeList:

Load distinct Attribute From GenericDB;

Set vFieldList1 = ;

Set vFieldList2 = ;

For vAttributeNo = 0 to NoOfRows('AttributeList') - 1

   Let vAttribute = Peek('Attribute',vAttributeNo,'AttributeList') ;

   Let vField = 'If(Attribute=' & chr(39) & vAttribute & chr(39) & ', Value)';

   Let vField1 = 'Concat(distinct ' & vField & ','';'') as ' & vAttribute;

   Let vFieldList1 = vFieldList1 & ',' & vField1 ;

   Let vField2 = 'If(Len(' & vAttribute & ')>0,Trim(Subfield(' & vAttribute & ','';''))) as ' & vAttribute;

   Let vFieldList2 = vFieldList2 & ',' & vField2 ;

Next vAttributeNo

GenericDB:

Load Key

  $(vFieldList2);

Load Key

  $(vFieldList1)

  From GenericDB Group By Key;

[Anonymous]

Thanks

[JoaquinLazaro]

Fantastic, HIC.

[rwunderlich]

I find a slightly easier form of the "ReJoin" scriptlet to be iterating the tablelist backwards because it requires only one FOR-NEXT loop:

CombinedGenericTable:

Load distinct Key From GenericDB;

FOR i = NoOfTables()-1 to 0 STEP -1

  LET vTable=TableName($(i));

  IF WildMatch('$(vTable)', 'GenericLabel.*') THEN

  LEFT JOIN ([CombinedGenericTable]) LOAD * RESIDENT [$(vTable)];

  DROP TABLE [$(vTable)];

  ENDIF

NEXT i

Or if you are using QVC:

CALL Qvc.JoinGenericTables('CombinedGenericTable', 'GenericLabel');

[#11]

We've found a number of uses for this Generic load - mainly around Question and Answer pairs for Customer Surveys. It make the analysis of each of the customers responses much easier, and allows for a very speedy solution.

[prodanov]

Hi

     This is verry good post for this powerful function. But my experian with this function say, that in the script to make 'combine new table' we must use OUTER JOIN, not LEFT JOIN. Because in source table some of attributes can be without value.

Best regards

Dimitar