Calculation in Load script

[#1]

Hi,

I am finding dificulty in creating below requirment in QVD loading script.

I am able to achieve same requirement in qvw using Set analysis but we need below requirement to be  implemented in qvd script itself.

Attached please find the sample file. "Cal Field" which needs to be derive from remaining fields.

here is the requirment.

Cal Field= if Business yr= 2013 and  then Cal field =( Business Year 2013 Amount - Business Year 2012 of Indicator='Y' Amount)

                                                                                          /   ( Business year 2012 of Indicator='Y' Amount))

e.g: Row 2 in the excel.

         Cal Field= (15683 - 36651)/ 36651 = -0.5721

Row 3 in the excel.

         Cal Field= (86253 - 36651)/ 36651 = 1.35336

Cal Field= if Business yr= 2012 and then Cal field =( Business Year 2012 Amount - Business Year 2011 of Indicator='Y' Amount)

                                                                                     / ( Business year 2011 of Indicator='Y' Amount))

Row 5 in the excel .

Cal Field = (86453 - 23135)/ 23135= 2.736892.

Please help me to reslove this .

[JonnyPoole · Accepted solution]

Here is my sample.  I pull only the business year and indicator where indicator=y. Add 1 to the business year and join that back to the main table . Then you can do the expression. Values seem to match your expression in the output. hope it helps.

Temp:

LOAD [Account Name],

     Date,

     Indicator,

     Flag,

     [Business Year],

     Amount,

     [cal field]

FROM

(biff, embedded labels, table is Sheet1$);

join (Temp)

LOAD [Account Name],

     [Business Year] +1 as [Business Year] ,

     Amount as Operand

FROM

(biff, embedded labels, table is Sheet1$)

where Indicator='Y';

Data:

load

  *,

  (Amount-Operand)/Operand as Cal

Resident Temp;

drop table Temp;

store Data into ./Data.txt (txt);

[#3]

Attached with answer. Please take a look at the back-end script on how to approach this.

Thanks