Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
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