Missing values when using Evaluate() in load script

[#1]

I'm trying to solve an odd issue where using Evaluate() in my load script is working most of the time, but failing consistently some of the time. I'm using Evaluate() to calculate the result of a mathematical function stored in the database, presenting the final value to the user in a chart.

In more detail, in this particular use case, there is a set of mathematical functions stored in a database that contain placeholders for data. I am loading this data from the SQL server, performing the proper replacements on the placeholders, and then reloading that data in QlikView using Evaluate() to get the final result of the formula. However, while this works in ~80% of all cases, it fails in large sections of results without any real rhyme or reason. However, it does fail consistently (the same values will be null after every reload), and it will be sections of null values, not randomly interspersed throughout the data set. Here is what I'm using in the load script:

RawData:
SQL
SELECT deliv.field_id AS 'Field ID'
  ,flow_date AS CalendarDate
  ,REPLACE(curve.field_curve, 'PLACEHOLDER', balance/1000000) AS 'Curve'
FROM
  field_deliverability deliv
LEFT JOIN
  field_deliverability_curve_detail curve
  ON
  curve.field_id = deliv.field_id
  AND curve.direction = deliv.direction
  AND deliv.balance BETWEEN curve.start_balance AND curve.end_balance;

Deliverability:
LOAD
  *
  ,Evaluate([Curve]) * 1000 AS 'Curve Result'
Resident
  RawData;

DROP Table RawData;

Originally I was thinking that it may have been an odd cross-join induced error, but when I remove everything else and just have a data island, I still see the same results. As an example, I will see results like the following:

Date	Formula	Result	Expected Result
12/31/2013	(-4.60*10.04489400000000+214.4)*1000	-	168,193
12/31/2013	(0.0*10.04489400000000+290)*1000	-	290,000
1/1/2014	(-4.60*9.94811400000000+214.4)*1000	158,691	168,638
1/1/2014	(0.0*9.94811400000000+290)*1000	295,000	290,000

Has anybody else experienced anything like this? Any tips on how to solve this?

Updated the formulas I had copied and pasted.

[swuehl]

I must admit I haven't used Evaluate() for some months now.

If you suspect an issue with the software, it might be helpful to know what version you are running.

And it would be really helpful if you could post a small sample app that demonstrates this issue (if you can't post your real data, try to create some mock up. Most of the time, while I am trying to reproduce the issue, I learn the reason why it won't work. So chances are you don't need to post anything ;-).

[#3]

Thanks swuehl, I'm using 11.0 SR1 (that particular version due to client requirements).

I've actually been working on a mini-example of it, but haven't been able to reproduce it so far using generated data. I'm going to continue working at it, but wanted to get the question out there just in case anybody had a similar experience.

[swuehl]

Sure, chances are that someone knows already what's going on.

But why do you expect 168,193 for a calculation like

-4.60*10.04489400000000+214.4*1000

I do get 214,354 which seems reasonable to me.

[#5]

My copy and paste mistake. Added parens around the formula to make it more clear (the *1000 is being performed in the load script just after Evaluate, wanted to call it out). The issue isn't math being done wrong, the issue is that math just isn't being done, giving a null result.

[swuehl]

So what about the difference between Expected Result and Result?

I tried to evaluate() the strings you posted above, all returned values that matched your Expected Result.

[#7]

When I Evaluate() them in a textbox they work just fine as well. It is only during load that some do not evaluate. That's the mystery.

[swuehl]

Could it be that your strings contain some special chars that can't be evaluate()ed?

[#9]

It's possible, but like in the examples I listed, the formulas are very often repeated with just the placeholder value changing, and some will evaluate while others will not. 😕

[swuehl]

The only thing that seems to be different between your working and non-working strings is the number of digits of your replaced floating point value.

It does work at my place regardless of that number, but could you try to limit the decimal places?