Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Missing values when using Evaluate() in load script

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:

DateFormulaResultExpected 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)*1000158,691168,638
1/1/2014(0.0*9.94811400000000+290)*1000295,000290,000

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

Updated the formulas I had copied and pasted.

Tags (3)
9 Replies
MVP
MVP

Re: Missing values when using Evaluate() in load script

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

Not applicable

Re: Missing values when using Evaluate() in load script

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.

MVP
MVP

Re: Missing values when using Evaluate() in load script

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.

Not applicable

Re: Missing values when using Evaluate() in load script

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.

MVP
MVP

Re: Missing values when using Evaluate() in load script

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.

Not applicable

Re: Missing values when using Evaluate() in load script

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.

MVP
MVP

Re: Missing values when using Evaluate() in load script

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

Not applicable

Re: Missing values when using Evaluate() in load script

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. :/

MVP
MVP

Re: Missing values when using Evaluate() in load script

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?

Community Browser