Oktober 2012 - No FACTS? Frenzel GmbH!

Sample Scheme and Data for KPI Dictionary

I build a small MYSQL Table to store the following fields:

Label

Definition

Description

Valid

Inside the Definition I use a „special“ syntax, so QlikView won’t crash while loading!

count({§<ActionType={"UnitMoveout"}>}%ActionID)

1	count({§<ActionType={"UnitMoveout"}>}%ActionID)

As you can see, I replaced the $-Sign with a §-Sign as the $ would be missinterpreted during the load process!
You can easily „replace“ the sign in the load statement!

Sometimes it could be very usefull to generate variable dynamicaly, in QlikView you can do this easily with a trick inside the load statement of you application!
Here is a short example (will give you some examples for a smart usage later):

TRACE --- LOAD ACTION AND EVENT ---;

VariableTable:
LOAD DISTINCT 
	 tblActionType.ActionType AS VariableName 
FROM
[..\04 DataMart\MMACTIONS.qvd]
(qvd)
WHERE Match( tblActionType.ActionType,'UnitMovein')&gt;0;

TRACE --- Generate variables for each ActionType ---;

//Get the number of available rows 
LET NoVariable = NoOfRows('VariableTable');

TRACE Number of Dynamic Vars: $(NoVariable);

//Lets build the dynamic variables
FOR DatenbankIter = 0 TO $(NoVariable) - 1

	LET curVariableName = peek('VariableName',DatenbankIter,'VariableTable');
	LET $(curVariableName)= peek('VariableName',DatenbankIter,'VariableTable');

NEXT

//jetzt entferne ich die Variablentabelle wieder
DROP TABLE VariableTable;

TRACE --- LOAD ACTION AND EVENT ---;

VariableTable:

LOAD DISTINCT

tblActionType.ActionType AS VariableName

FROM

[..\04 DataMart\MMACTIONS.qvd]

(qvd)

WHERE Match( tblActionType.ActionType,'UnitMovein')>0;

TRACE --- Generate variables for each ActionType ---;

//Get the number of available rows

LET NoVariable = NoOfRows('VariableTable');

TRACE Number of Dynamic Vars: $(NoVariable);

//Lets build the dynamic variables

FOR DatenbankIter = 0 TO $(NoVariable) - 1

LET curVariableName = peek('VariableName',DatenbankIter,'VariableTable');

LET $(curVariableName)= peek('VariableName',DatenbankIter,'VariableTable');

//jetzt entferne ich die Variablentabelle wieder

DROP TABLE VariableTable;

Monat: Oktober 2012

Building and Using a KPI Dictionary

Sample Scheme and Data for KPI Dictionary

Usage

QlikView Generate Dynamic Variables