Using variables in load script

[bhaskarsharma03]

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

[petter]

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.