Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculation in Load script


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 .


 

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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);

View solution in original post

2 Replies
JonnyPoole
Employee
Employee

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);

Not applicable
Author

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

Thanks