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

[cooper_yonk]

I know this is an old post but i'm trying to follow the above but im getting confused, and would like some help.  Here is my script.  The first part of the script creates all my bespoke tables...but I want to consolidate them into a single table as per Henric's post, but I cant get it to work.  The first part works and creates multiple tables for each of my BespokeDefinition but then it says table not found.  Does anyone know what i'm doing wrong?

SET v.GenericTableName = 'BespokeItem';
//
[$(v.GenericTableName)]:
Generic
LOAD
CommonRef,
ReferralRef,
BespokeDefinition,
BespokeItem,
SchemeName,
ServiceDesc,
BespokeFieldCaption,
BespokeFieldControlType
FROM [Q:\Production\Data\Source\CQ QVDs\BespokeFieldItems.QVD] (qvd);

Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='$(v.GenericTableName)' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo

CombinedGenericTable:

Load distinct BespokeDefinition resident [$(vTableName)];
For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName

[choice1848]

Dear Henrich,

I always get an error message "Fiel 'Artcle_PK' not found" at "CombinedGenericTable: Load distinct ARTICLE_PK_FROM...".

The script is as follows:

SET LongDayNames='Montag;Dienstag;Mittwoch;Donnerstag;Freitag;Samstag;Sonntag';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';

// Load distinct PROPERTY
// FROM [lib://Qlik_QVDs/Testdaten/pscdb_articles_properties.qvd];

Properties:
Generic
LOAD

ARTICLE_PK,
// "TYPE",
PROPERTY,
VALUE
FROM [lib://Qlik_QVDs/Testdaten/pscdb_articles_properties.qvd]
(qvd)
where PROPERTY = 'eclass' or PROPERTY ='eclass_info';



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 ARTICLE_PK FROM [lib://Qlik_QVDs/Testdaten/pscdb_articles_properties.qvd];


For each vTableName in $(vListOfTables)

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

Drop Table [$(vTableName)];

Next vTableName

exit script;

Do you have any idea what I am doing wrong?

Thanks in advance

[Qlik-Developer]

Hi @hic ,

I have data like below, id and created are the qualifier fields, values under the "field" column should convert as columns and remaining filed values should be like values.

values under field column should pivot as  columns like status,sprint,rank,attachment etc.....

i have written like below,I need id ,created columns as it is and "field" column values should pivot as columns and "fromstring", "tostring" values should be corresponding to the above three dimensions.

tableA:

generic load

id,
created,
"field",
"fromString",
"toString"

from XLSXL;

I couldn't able to load because my value are in multiple fields so how can I do it?

[draghici1109]

The following line of code "Load distinct Key From GenericDB;" suggests that somewhere there is a file with the name "GenericDB" where data resides. It seems that GenericDB is the file name of the input table, as in the first picture in the article.

For me, as a rookie it was difficult to follow the explanation in this article, as at first I could not associate GenericDB with anything. Meanwhile it seems to me very probabely that the file "GenericDB"has the following structure:

Key | Attribute | Values

For the sake of completeness one might put this remark into the original article. It would ease the great reasoning contained in this article and might save some time of rookie readers like me. 

[max200560]

Sorry but for me it is very difficult (I'm new to qlink), also in the table I found a lot of incomplete data so I abandoned the project

[petterhuseby]

Hi

Here is my working example, maybe it can be used for clarification. I have used some variable, so this must be changed to your ovn values. 

GenericDB:
generic select sathashkey as "#dim_$(hub4)_key",
$(attributter4)
from UDI_adm.sat_$(sat4)
where date_expire is null;

Set vListOfTables = ;
For vTableNo = 0 to NoOfTables()
Let vTableName = TableName($(vTableNo)) ;
If Subfield(vTableName,'.',1)='GenericDB' Then
Let vListOfTables = vListOfTables & If(Len(vListOfTables)>0,',') & Chr(39) & vTableName & Chr(39) ;
End If
Next vTableNo
trace $(vTableNo);

CombinedGenericTable:

select distinct sathashkey as "#dim_$(hub4)_key"
from UDI_adm.sat_$(sat4)
where date_expire is null;

For each vTableName in $(vListOfTables)
Left Join (CombinedGenericTable) Load * Resident [$(vTableName)];
Drop Table [$(vTableName)];
Next vTableName

[jamielim]

Hi @hic , 

Thanks for this post. It works well for my case when I have less than or 100 distinct values for the key, or in my case the rowID. when I have more than 100distinct values for the rowID, I find that the generic load function only returns 100 lines. Am I missing something here? 

Part of my data load script pertaining to the Generic Load function is below. The cells 

[cells]:
LOAD
[__FK_cells] as rowID,
[virtualColumnId],
[value]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_cells]);

left join (cells)
LOAD [virtualId] as [virtualColumnId],
[title]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_columns]);

Table:
generic Load rowID, [title],[value]
resident [cells];

Thanks for looking. Any advice is much appreciated!

Regards,

Jamie

[hic]

There should be no limit at 100 rows. So if you expect more than 100 rows, then something is obviously wrong.

What happens if you delete (or comment) the Generic Load, and look at the "cells" table that you use as input for the Generic load: How many distinct rowID:s does it have?

[jamielim]

Thanks @hic . Yes, you are right. The cells table also has 100 distinct rowIDs.

Is this due to the configuration of the REST Connection then? I'm connecting to Smartsheet using the REST connection. Is there something I need to  as Query parameter or Pagination setup? I'm not too sure. 

I have the following set up:

Regards,

Jamie

[hic]

I don't know the REST connector well enough to answer, sorry.

However, as a debug effort, I would try the following simple Load, just to see the number of rows, and the number of distinct values in the different fields. If you get more than 100 rows, then you know that the REST connector can deliver more than 100 rows, and the problem is somewhere else.  

The number of distinct values would be test #2. If you see that [__FK_cells] only has 100 values, then the problem could be your data.

RawData:
LOAD [__FK_cells] as rowID, [virtualColumnId], [virtualId],
title, [value]
RESIDENT RestConnectorMasterTable ;