Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Handling Metadata in QlikView

sinanozdemir
Valued Contributor III

Handling Metadata in QlikView

When it comes to providing metadata, QlikView allows users to provide table, column and field definitions by using COMMENT in the load script. This is beneficial for analysts, designers, and developers. In the below example, I will be going through a simple exercise.

1.PNG

After we load this, the data model looks like the below. When we hover over the table, we can see some basic information about this table, such as number of rows, fields, and keys. Although, this wouldn’t be enough for someone who doesn’t really deal with data modelling.

2.png

We can enhance this data model by adding metadata.  I also brought in another table called “Sales Person” so that I can elaborate on how to show metadata for more than one table:

3.PNG

First, we created a mapping table called “Table Map” which contains information about the tables in the data model. This could be an Excel file, or a table/view from a database. At the end of the second table, I used COMMENT to display information about these tables. After re-loading the script, we can see that above descriptions appear in the data model:

4.png

5.png

We can repeat the same process to display metadata for each field in the data model:

6.PNG

When we hover over the fields in the data model, we can see the descriptions of each field:

7.png

We can even tag each field as measure or dimension which helps users greatly to determine which fields to use as dimensions or measures:

8.PNG

As a result of this, dimensions and measures appear on the top of the field list when creating a chart:

8.5.png

9.png

Hope this helps everyone and I am attaching a blank qvw that has the above codes in the load script.

Thank you.

Other Published Documents on Qlik Community by Sinan:

QlikView and Python Integration

QlikView Automation Series – Unzip & Load Excel Files:

Tags (1)
Attachments
Comments
Not applicable

Thanks Sinan

Very helpful to make any QV or QS App more comprehensive. Especially in multi-language environments such features come in very handy. I will add this in the future into my "language/kpi-complex" of every QV. In QS I'm still testing how to set up such a multi-language structure, as a partially central kpi repository is part of the basic QS functionality.

Best Regards

Chris

kkkumar82
Valued Contributor III

Excellent post, only one doubt for showing fields as dimensions and measures is &needed in the table.

Thanks

Kiran kumar

This is amazing. I didn't even know that such a thing existed. Thanks Sinan for introducing to a new concept.

Best,

Sunny

lubicz99
Valued Contributor

Great info Sinan.  Not a day goes by that I don't learn something useful in this community.  Will be bookmarking this one for future reference.

Not applicable

Let me give you some more insights into the above structure (the credit of the idea goes, as I learned from him, to Clemens Zöchmeister of Qlik Germany):

It's purpose is mainly to reduce maintenance issues and enforce master data management. When the number of your Qlikview applications grows, you will be happy to have implemented structures that help with keeping control over all your formulaes, text blocks such as comments, field and table names - especially in a multi-lingual environment.

At a first inclination you would implement such things in all the QV objects of your application canvas the hard coding way. Thus, whenever such elements (formulaes, text blocks, even colors, etc.) would change, you would need 1) to find all the places you used such elements throughout all your different applications, 2) change all the impacted instances. This leads quite surely to an impressive workload - with no certainty, that you caught up with all the places you used a certain formula. Supported documentation of your QV applications within Qlikview is not yet at it's peak

So, that's the trade-off, you invest in a higher degree of abstraction within the application canvas and it's objects such as listboxes, diagrams, etc. E.g. for listbox titles you would introduce an external table where you keep all the instances stored. Whenever there is a change, you just change the values in this table - and in all the objects the change is updated with the next loading/publishing.

Let's go for an example (for the part of the tables 'Kennzahlen' and 'Kennzahlen_Katalog', and the implementation within your QV worksheet and objects).

The table (here a simple excel sheet for the KPIs):

The script in your QV application would look as follows for loading the KPI table

//***********************************************************************************************************************************

//

// Sales Cockpit & Cost Controlling Application

//

//***********************************************************************************************************************************

// Register

//

// KPIs Loading        -  Load KPI repository and comments

// Source: \\...\SourceDocuments\VM\00_source\005_metadata\Kennzahlen.xlsx

//        

// Application: Qlikview Sales Cockpit & SAP Cost Controlling for the Sales Organizations

// Level:       Model

//

// Author: Christoph Schlunegger

// Organization: Vertriebsplanung & -controlling

// Date: 13.11.2014

// Version: v1.0

//

//

// Copyright 2014 by Christoph Schlunegger

//

//***********************************************************************************************************************************//***********************************************************************************************************************************

//

// Kennzahlen-Repository laden und Kommentare aufbereiten für Übersetzungen

Kennzahlen:

LOAD KPI_ID,

     KPI_Formula,

     KPI_Bezeichnung_dt,

     KPI_Bezeichnung_fr,

     KPI_Bezeichnung_it,

     KPI_Bezeichnung_en,

     KPI_Kommentar_dt,

     KPI_Kommentar_fr,

     KPI_Kommentar_it,

     KPI_Kommentar_en,

     Applikation,

     '$(vScript_dat_Ladedatum)'         as KPI_Ladedatum,

     '$(vScript_dat_today)'             as KPI_Gueltigkeitsdatum

FROM

[$(vScript_rootfolder_metadata)\Kennzahlen.xlsx]

(ooxml, embedded labels, table is Tabelle1)

WHERE Applikation like 'SAP';

store Kennzahlen into [$(vScript_SAPCC_builderdata)\SAP_Kennzahlen_data.qvd];

// Kein Loader vorhanden

store Kennzahlen into [$(vScript_SAPCC_loaderdata)\SAP_Kennzahlen_data.qvd];

DROP Table Kennzahlen;

// Kennzahlen laden aus dem abgespeichertem Kennzahlen-Repository

Kennzahlen_Katalog_Temp:

LOAD KPI_ID,

     KPI_Formula,

     KPI_Bezeichnung_dt,

     KPI_Bezeichnung_fr,

     KPI_Bezeichnung_it,

     KPI_Bezeichnung_en,

     KPI_Kommentar_dt,

     KPI_Kommentar_fr,

     KPI_Kommentar_it,

     KPI_Kommentar_en,

     Applikation,

     KPI_Ladedatum

FROM [$(vScript_SAPCC_loaderdata)\SAP_Kennzahlen_data.qvd] (qvd);

// Tabelle nun in Variablen konvertieren

let vScript_RowCountVariables = NumMax(NoOfRows('Kennzahlen_Katalog_Temp'),0)-1;

for vScript_i = 0 to '$(vScript_RowCountVariables)'

    let vScript_TempVarName = 'vKPI_'&peek('KPI_ID',$(vScript_i), 'Kennzahlen_Katalog_Temp');

    let vScript_TempVarValue = peek('KPI_Formula', $(vScript_i), 'Kennzahlen_Katalog_Temp');

    let $(vScript_TempVarName) = '$(vScript_TempVarValue)';

   

next vScript_i;

// Transformiere die KPI-Tabelle in eine Multiliguales Modell "Crosstable Load"

Kennzahlen_Katalog:

LOAD KPI_ID,

     KPI_Formula,

     KPI_Bezeichnung_dt    as KPI_Bezeichnung,

//     KPI_Bezeichnung_fr,

//     KPI_Bezeichnung_it,

//     KPI_Bezeichnung_en,

     KPI_Kommentar_dt      as KPI_Kommentar,

//     KPI_Kommentar_fr,

//     KPI_Kommentar_it,

//     KPI_Kommentar_en,

//     Applikation,

//     KPI_Ladedatum,

     'dt'                  as Sprache

RESIDENT Kennzahlen_Katalog_Temp;

for each vScript_Loop_KPI_Labels in 'fr','it','en'

    Concatenate(Kennzahlen_Katalog)

    LOAD

         KPI_ID,

         KPI_Formula,

         KPI_Bezeichnung_$(vScript_Loop_KPI_Labels)    as KPI_Bezeichnung,

         KPI_Kommentar_$(vScript_Loop_KPI_Labels)      as KPI_Kommentar,

         '$(vScript_Loop_KPI_Labels)'                  as %Sprache

    RESIDENT Kennzahlen_Katalog_Temp;

next vScript_Loop_KPI_Labels;

DROP Table Kennzahlen_Katalog_Temp;

//KPI-Tabelle umwandeln in eine Kreuztabelle

KPI_ID_List_Temp:

LOAD distinct KPI_ID as KPI_ID_List_temp

RESIDENT Kennzahlen_Katalog;

for vScript_m = 0 to '$(vScript_RowCountVariables)'

    let vScript_Loop_KPI_ID = peek('KPI_ID_List_temp', $(vScript_m), 'KPI_ID_List_Temp');

    if $(vScript_m) = 0 then

       Kennzahlen:

       LOAD

            %Sprache,

            KPI_Formula                    as KPI_$(vScript_Loop_KPI_ID),              // Formel

            KPI_Bezeichnung                as KPI_$(vScript_Loop_KPI_ID)_Bezeichnung,

            KPI_Kommentar                  as KPI_$(vScript_Loop_KPI_ID)_Kommentar

       RESIDENT Kennzahlen_Katalog

       WHERE KPI_ID = '$(vScript_Loop_KPI_ID)';

      

    ELSE

       left join (Kennzahlen)

       LOAD

            %Sprache,

            KPI_Formula                    as KPI_$(vScript_Loop_KPI_ID),              // Formel

            KPI_Bezeichnung                as KPI_$(vScript_Loop_KPI_ID)_Bezeichnung,

            KPI_Kommentar                  as KPI_$(vScript_Loop_KPI_ID)_Kommentar

       RESIDENT Kennzahlen_Katalog

       WHERE KPI_ID = '$(vScript_Loop_KPI_ID)';

      

    ENDIF;

next vScript_m;

DROP Table KPI_ID_List_Temp;

      

store Kennzahlen into [$(vScript_SAPCC_cleanerdata)\SAP_Kennzahlen_data.qvd];

store Kennzahlen_Katalog into [$(vScript_SAPCC_cleanerdata)\SAP_Kennzahlen_Katalog_data.qvd];

//***********************************************************************************************************************************

In the object's definition window this would look as follows (top shows the title bar of the diagramm object on the work sheet, then the definition window of the diagramm object and then the formula definition window):

I agree, you could even go a step further and also replace the values for TransaktionsType and KPIType with abstractions.

Regards

Chris

Siva_Sankar
Honored Contributor

sinanozdemir

The comments for table is working for some and not for some. I have posted a discussion here Metadata handling not working properly

Can you help on this?

Regards,

Siva Sankar

bhavinimehta
New Contributor III

Excellent post

Thanks

-Bhavini

Version history
Revision #:
1 of 1
Last update:
‎12-30-2015 05:06 PM
Updated by: