Qlik Community

Ask a Question

Qlik Design Blog

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

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
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

45 Comments
francisco_javie
Contributor
Contributor

Hi,

I am creating a data model use the genric load statement but I have a complication, let me explain.

The DB source is very simple has one fact table and two dimensional tables.

First dimensional table is related with  production sites

Plant IDPlantNamePlant TypeCountryRegion
1Plant A Type 1 Italy Europe
2Plant BType 1FranceEurope
3Plant CType 2BelgiumEurope

Second dimensional table is related with the metrics calculated per site

Metric IDMetricNameMetric TypeMetric UnitsPlant ID
1Metric A Type 1Units A1
2Metric BType 2Unit B1
3Metric BType 2Unit B2

The fact table contains the daily value for each metric for all production sites

Metric IDMetricValuePlant IDDate
1Metric A 101-01-17
2Metric B 101-01-17
3Metric B 201-01-17

In the load script all map is done so the IDs columns are replaced by the names

Unfortunately the fact table can not be used as it is due to several reasons. As example the agregation rules can vary by metric.

Therefore I use the generic statement to get a table per metric . When doing so the system create a syntethic key composed by Date+PlantName+MetricName. This key works well to create apps as value tables and dimension tables (production sites table and metric dimensional table) are linked, but I can not create any calculated measure (e.g. Metric A + Metric B) in an app  as they do not share any comom key. I can overcome the problem by creating a shorther Key  Date+PlantName which will allow me to create calculated measures (e.g. Metric A + Metric B). Unfortunately then the link between the values tables and metric dimensional table is nonexisting.

Is there a way to have all functionalities available?

Thanks a lot

Paco

0 Likes
839 Views
carlcimino
Creator II
Creator II

Hello, I'm am trying to accomplish the part where "The result is one big table that contains all attributes" so it can be used to join to other tables.  The values for SOURCE_SYSTEM are PLS, MP and ORCA.  Having trouble with the part to do the FOR-NEXT loop.  Here's what I have but it gives me the error below.  Any help would be appreciated.  Thanks!

step1:

generic LOAD

#POL_N as POL_N,

Autonumber(SOURCE_SYSTEM & '|' & SOURCE_SYSTEM & '|' & SOURCE_SYSTEM) as Key,

trim(SOURCE_SYSTEM) as SOURCE_SYSTEM,

DEVICE_VALUE

FROM

(qvd)

WHERE(DEVICE_TYPE = 'E-MAIL ADDRESS');

Set vListOfTables = 3;

  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 step1;

  For each vTableName in $(vListOfTables)

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

  Drop Table [$(vTableName)];

  Next vTableName

0 Likes
839 Views
gino2780
Creator
Creator

Hello Henric,

i appreciate your blog entry. It motivated me to reproduce your example simply without a Loop. I hope it is okay, when i share my work here. If not please let me know.

GenericDB_Dashboard.JPG

Greets,

gino2780

0 Likes
839 Views
tony_thompson
Partner
Partner

Trying to add an excel sheet that which i cannot.

have a similar situation of loading as a cross table with multiple header.

please help

tony

       

Baseline Qty Baseline Price (Final, average of shortlisted suppliers if no baseline price) Baseline Spend
MB IDProduct CategorySupplierSupplier Part NumberManufacturer/ BrandManufacturer Part NumberStock CodeItem NameItem DescriptionUoPUoIQty/UOPPlant1Plant2Plant3Plant4Plant5Plant6Plant7Plant8Plant9Plant10 Estimated Annual Qty Baseline Qty SourceCommentsPlant1Plant2Plant3Plant4Plant5Plant6Plant7Plant8Plant9Plant10Baseline Price SourcePlant1Plant2Plant3Plant4Plant5Plant6Plant7Plant8Plant9Plant10 Item Level
Baseline Spend
1536VALVESup2SS2P-25MM00000094532VALVE,BALL:25MM F.BSP;SS316 BODY,BALL & STEM;  TYPE K2(2-PIECE);    EA 178109 34               34            255 INVOICE $20.96$20.96$0.00$20.96$0.00$0.00$0.00$0.00$0.00$20.96 $ 1,635 $ 2,285   $               713         $               713 $ 5,345
107FITTINGSSup3100001537500000027516COUPLING,PIPE:50MM F.BSP X 50MM F.BSP; STEEL; GALV;  AS1074 SPEC2IN; EN 10241EA 17429 1860               33            214 INVOICE $2.85$2.85$0.00$2.85$2.85$0.00$0.00$0.00$0.00$3.09 $               211 $                  83   $                  51 $               171       $               102 $               618
115FITTINGSSup3100001466000000027706TEE,PIPE:50MM F.BSP EQUAL,MALL IRON,GALV,        2450KPA,AS3673 SPEC                     2INCH EQUALEA 18027 2514               15            161 INVOICE $5.61$5.61$0.00$5.61$5.61$0.00$0.00$0.00$0.00$6.09 $               449 $               151   $               140 $                  79       $                  91 $               910
1139PIPESup1CHS-25-M-GAL-TBE-650000000004283
000079509
000155362
PIPE,METALLIC:25MM NB;6.5M LGTH;STEEL;PLAIN ENDS;GALV;MEDIUM;EA 1160107 531215 84              64            423 INVOICE $35.70$29.09$0.00$29.09$41.66$41.66$0.00$29.09$29.09$29.09 $ 5,712 $ 3,113   $ 1,542 $               500 $               625   $               233 $ 1,862 $ 13,586
1141PIPESup3100003298800000079525PIPE,METALLIC:32MM NB;6.5M LGTH;SCREWED;STEEL;GALV;   MED GAUGE;3.2MM WALL THK;3.22KG/M; AS1074;EA 117482 46314             113            432 INVOICE $43.93$36.96$0.00$36.96$43.93$43.93$0.00$0.00$0.00$36.96 $ 7,644 $ 3,031   $ 1,700 $               132 $               615     $ 4,176 $ 17,298
MB IDAttributeProduct CategorySupplierSupplier Part Number Baseline Qty MB IDAttribute Baseline Price MB IDAttributeForecast Spread
1536Plant1 781536Plant1781536Plant11634.88
1536Plant2 1091536Plant21091536Plant22284.64
1536Plant3 01536Plant301536Plant3
1536Plant4 341536Plant4341536Plant4712.64
1536Plant5 01536Plant501536Plant50
1536Plant6 01536Plant601536Plant60
1536Plant7 01536Plant701536Plant7
1536Plant8 01536Plant801536Plant80
1536Plant9 01536Plant901536Plant90
1536Plant10 341536Plant10341536Plant10712.64
107Plant1 74107Plant174107Plant1210.9
107Plant2 29107Plant229107Plant282.65
107Plant3 0107Plant30107Plant3
0 Likes
839 Views
cooper_yonk
Contributor
Contributor

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

0 Likes
541 Views
choice1848
Creator II
Creator II

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

198 Views