Using variables in load script

bhaskarsharma03 — Thu, 23 Nov 2017 08:47:12 GMT

Hi,

I want to use variable in the load script which calculates values based on previously loaded table. In the given example:

--> Data table contains my master data

--> KPI_DEF table has got definitions for KPIs to be used in the data model

--> KPI table is where I want to calculate the KPIs for each of the records for in the Data table

The KPI_Definition field in the KPI table is giving me the formula for the KPIs but not the actual calculated value (which is what I need).

Any help will be greatly appreciated.

/**/

Data:

LOAD * Inline [

Dim, Sales, COS

A, 50, 20

B, 20, 10

C, 80, 40

D, 100, 45

];

KPI_DEF:

LOAD * Inline [

KPI, Definitions

vTotalSales, '=Sales*100'

vTotalCOS, '=COS*200'

];

For i = 0 to NoOfRows('KPI_DEF')-1

LET vKPI = Peek('KPI', i, 'KPI_DEF');

LET vKPIDef = Peek('Definitions', i, 'KPI_DEF');

KPI:

load

Dim

,'$(vKPI)' AS KPI

,'$(vKPIDef)' AS KPI_Definition

Resident Data;

Next i

Drop Table KPI_DEF;

/**/

Regards,

Bhaskar

Re: Using variables in load script

petter — Thu, 23 Nov 2017 16:33:59 GMT

Data:
LOAD * Inline [
     Dim, Sales, COS
     A, 50, 20
     B, 20, 10
     C, 80, 40
     D, 100, 45
];

KPI_DEF:
LOAD * Inline [
     KPI, Definitions
     vTotalSales,  'Sales*100'
     vTotalCOS,  'COS*200'
];

For i = 0 to NoOfRows('KPI_DEF')-1
     LET vKPI = Peek('KPI', i, 'KPI_DEF');
     LET vKPIDef = Peek('Definitions', i, 'KPI_DEF');
KPI:
load
     Dim
     ,'$(vKPI)' AS KPI
     ,$(vKPIDef) AS KPI_Definition
Resident Data;

Next i
Drop Table KPI_DEF;

Have a look at how I changed the lines 13,14 and 24. In 13 and 14 you will have to remove the equal sign.

In line 24 the single quotes were removed.

topic Re: Using variables in load script in QlikView

Using variables in load script

Re: Using variables in load script