Load Script

[upaliwije]

I have following fields in my data table

Note     Amount

100      100

101      500

102      300

103      400

200      800

I want to create following variable in my load script

if Note=100 then sum amount   as X

if note =101 then sum amount  as Y

if Note =200 then sum amount as Z

I have written the load statement like this but it does noe work. Pls help me with correct script

if(NOTE='100',sum(AMOUNT))as X

[preminqlik]

vX=sum({1}Aggr(sum({<Note={100}>}Amount),Note))

[upaliwije]

It is not working in load script

[upaliwije]

Error in expression:

AGGR is not a valid function

TB:

LOAD BAL_ACCT_CODE,

     NOTE,

    CLA_CODE,

     AMOUNT,

    vX=sum({1}Aggr(sum({<Note={100}>}Amount),Note)),

     BCOD,

     Date(MonthEnd(Date#([YEAR] &'-'& [MONTH],'YYYY-MM')),'dd-MMM-yyyy')     AS [TRN_DATE]

FROM

[..\QVD\FACT_Table\Trial_Balance.qvd]

(qvd)

WHERE YEAR='2013'

[preminqlik]

you have to write in front end ...go to variable overview

[upaliwije]

sorry

I cant understand what you say. Pls explain

[preminqlik]

you have to define the variable in front end ...press ctrl+alt+v     ...and define over there

[upaliwije]

I did as you said and executed the script.

so where can I find the field VX

[preminqlik]

...vx is not a field ...it is a variable..... now u can use that in your expressions by naming $(vX)...send your requirement clearly...

[upaliwije]

TB:

LOAD BAL_ACCT_CODE,

     NOTE,

    CLA_CODE,

    if (CLA_CODE='MC','MOTOR',if(CLA_CODE='M3','MOTOR',if(CLA_CODE='M4','MOTOR','NON_MOTOR')))as   MONM,

     //YEAR,

     //MONTH,

     AMOUNT,

     BCOD,

     Date(MonthEnd(Date#([YEAR] &'-'& [MONTH],'YYYY-MM')),'dd-MMM-yyyy')     AS [TRN_DATE]

FROM

[..\QVD\FACT_Table\Trial_Balance.qvd]

(qvd)

WHERE YEAR='2013';

Dear Prem

The above is my data load script. Now I want to create a dimension fiels called 'PREMIUM' (that is sum of amount where note  ='100') in the load script. So that dimension I can use for my chart. That is what I want. Pls help