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

[ToniKautto]

@jamielim the result from a REST connection will depend on the API endpoint. Please refer to the documentation of the API end-point to see how you can extend page length, filter data or paginate through the data etc.

Qlik Help has some examples of how pagination might be configured depending on the API end point. 
https://help.qlik.com/en-US/connectors/Subsystems/REST_connector_help/Content/Connectors_REST/Create... 

[jamielim]

Hi @ToniKautto ,  I'm required to "specify the includeAll=true query string parameter" if I want all results. Where do I input the query string parameter? I'm very new to using REST connector. Appreciate if you could advise. Thanks!

[ToniKautto]

@jamielim I'd suggest raising a separate community thread if you are stuck getting your specific reload to function as expected. 
In short, you can alter this part under Query parameters in Additional request parameters section of the REST connection config dialog. 

[devrajR]

Hi, 

I have successfully executed the following statement:

GenericDB:
Generic Load [Key], [Attribute], [value] Resident AllSourceDataCombo_v2;

Multiple tables have been created. Next, when I add the following statement: 

AttributeList:
Load distinct Attribute Resident GenericDB;

I get an error: "The following error occurred: Table 'GenericDB' Not Found. The error occurred here: AttributeList: Load Distinct Attribute Resident GenericDB"

To counter this, I tried adding NoConcatenate after Drop Table Statement, but that gave "Illegal combination of prefixes The error occurred here: NoConcatenate"  error.

Could someone suggest what part is wrong here?

Thank You.

[peterwh]

Hello @devrajR,

the table name for the generic load (in our case GenericDB) is just a prefix for all tables that are created. You can see it in your screenshot of your data load progress window. There is a "GenericDB.w14.quotezone.co.uk"-Table, a "GenericDB.uk.search.yahoo.com"-table and so on.

There is no "GenericDB"-table after a generic load.

Kind regards

Peter

[devrajR]

Thank You @peterwh 

I now understood what is happening. But as in the original post, how would I get the output table after executing this code:

   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

[peterwh]

Hello @devrajR ,

the table which the generic load was used on was "GenericDB". In your case you have to do a resident load of "AllSourceDataCombo_v2".

CombinedGenericTable:
Load distinct Key Resident AllSourceDataCombo_v2;

I think that is the trick in your situation.

Kind regards

Peter

[devrajR]

Hi @peterwh 

Found a way to get the data from rows to columns. Thank You for helping with the error that I was facing.

[MT4T]

Hello,

I also have a problem with part "Load distinct Key From GenericDB".

I followed all comments, but this is unclear to me.  I know that this approach is not very efficient, but I think this necessary in my case. At the end of the day, I need the same pivoted table structure as the initial.

Because we use inphinity forms and i need separate fields for dropdowns, formats etc. We want to use this to convert few big excel spreadsheets into forms. We want to use applymap on Attribute and Value fields to rename cols and unify entered values to format which is needed to forms. This excel comes from external source behind our control, so changes are very often. We plan to use forms for maintaining these mapping tables so there will be no necessity to do script modification by developers in the future, users will maintain this. 

My script look like:

map:
MAPPING LOAD * INLINE [
lookup, substitute
-, 0
+, 1
];

[data_loaded]:

CROSSTABLE (ATRIBUTE,VALUE,1)

LOAD

 GLOBALNAME as GlobalName,

    ASSY as Assy, //

    "READY FOR SALES" as ReadyForSales, //

    EOL as Eql, //

    "REGULAR/ PROMO" as Regular_Promo, //

    Brand, //

    SMART as Smart

......

FROM [lib://EMARK:DataFiles/SCEP_LineUp_2023_05.xlsx]

(ooxml, embedded labels, table is [2023_LineUp]);

Generic:
generic Load
GlobalName,
ATRIBUTE,
ApplyMap('map', VALUE) as VALUE
RESIDENT data_loaded;

   Set vListOfTables = ;
   For vTableNo = 0 to NoOfTables()

      Let vTableName = TableName($(vTableNo)) ;

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

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

      End If

   Next vTableNo

 CombinedGenericTable:

   Load distinct GlobalName /*From Generic*/ /*RESIDENT '$(vTableName)'*/ FROM [lib://EMARK:DataFiles/SCEP_LineUp_2023_05.xlsx];

   For each vTableName in $(vListOfTables)

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

      Drop Table [$(vTableName)];

   Next vTableName

I use Qlik SaaS (Sense). 

[marcus_sommer]

It's just a dummy-name / reference to the existing table / load. To make it simpler I combined it with the example from the origin posting:

m: mapping load * inline [
L, R
-, 0
+, 1
];

// map F1, F2, F3 using m;

t1: crosstable(Attribute, Value, 1) load * inline [
Key, F1, F2, F3
1, -, -, +
2, +, -, -
3, -, -, -
4, +, +, +
];

t2: noconcatenate load Key, Attribute, applymap('m', Value) as Value resident t1;

t3: Generic Load Key, Attribute, Value resident t2;

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

CombinedGenericTable: Load distinct Key resident t2;

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

drop tables t1, t2;