Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
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 .


 

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Employee
Employee

Re: Calculation in Load script

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
Highlighted
Employee
Employee

Re: Calculation in Load script

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

Highlighted
Not applicable

Re: Calculation in Load script

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

Thanks