topic Re: Calculations in the Load Script in QlikView

Calculations in the Load Script

greend21 — Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to do a calculation in the load script but I keep getting an invalid expression error. I thought it could be something with the dates being timestamps so I used floor to drop it and make it a number but that doesn't seem to be correctiong the issue either. Do you have any idea what the issue could be?

Sum(fabs(AllocAmt)*(Floor(ClosedDate)-Floor(TransEfftDate)))/Sum(Floor(ClosedDate)-Floor(TransEfftDate))

Thank you,

Re: Calculations in the Load Script

vishsaggi — Tue, 03 Jul 2018 17:56:38 GMT

Can you share the script you are running and also a snapshot of your error msg? if you are using Sum() aggregate function are you using Group By in your script?

Re: Calculations in the Load Script

BalaBhaskar_Qlik — Tue, 03 Jul 2018 18:03:30 GMT

It'll throw invalid expression, cause data conversion not happened properly.

Create some variables like this and those.

Set vMaxInvoiceDay = Day($(vMaxInvoiceDate));

Set vDaysInMaxMonth = Day(MonthEnd(Month($(vMaxInvoiceDate))));

Set vRunRateFactor = ($(vDaysInMaxMonth) - $(vMaxInvoiceDay))/$(vMaxInvoiceDay);

Re: Calculations in the Load Script

greend21 — Tue, 03 Jul 2018 18:06:09 GMT

I completely forgot about the Group By. Since I am loading a lot of other fields I just did the calculations on a temp table and left joined them back. Thanks!