9 Replies Latest reply: Oct 29, 2013 6:33 PM by Stefan Wühl RSS

    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.