Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
Join us March 10th, 7 Ways Modern Analytics Can Help You Take Smarter Action. REGISTER NOW
Henric_Cronström

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

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.

 

Generic transformation4.png

 

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:

 

Multiple keys3.png

 

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

44 Comments
kalyandg
Partner
Partner

hi HIC,

very nice

Best Regards,

Kalyan.D

0 Likes
35,990 Views
Not applicable

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.

0 Likes
35,988 Views
Henric_Cronström

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

0 Likes
35,989 Views
Not applicable

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.

0 Likes
35,989 Views
Henric_Cronström

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;

35,990 Views
egnaldo_sousa
Creator II
Creator II

Thanks

0 Likes
35,990 Views