topic How do I get evaluate() to work? in QlikView

How do I get evaluate() to work?

gussfish — Mon, 02 May 2011 04:12:11 GMT

Hi everyone,

I'm trying to get evaluate() to work for me in a QlikView 10 script, but it will only return me NULL!

Ultimately, I'm trying to load a glossary from a spreadsheet with two columns (Term, Definition), and want Definition text to be allowed to be expressions (including variable references )that are evaluated at Load time. This is my script, with a couple of extra fields, test1 and test2, I've added in my attempts to get evaluate() to work.


Definitions:
LOAD
 Term,
 if(isnull(evaluate(Definition)),Definition,evaluate(Definition)) AS Definition,

 // a couple of attempts to get meaningful results from evaluate()
 evaluate(5*8) as test1, // this is the evaluate() example in the QV10 Reference Manual - though it seems to be an incorrect example to me, since the parameter is meant to be a string.
 evaluate('5*8') as test2
FROM
[Definitions.xls]
(biff, embedded labels, table is Definitions$);

The resulting table, though, has NULLs for every record in the Definition, test1 and test2 columns.

Am I not understanding something, or is evaluate() broken?

Thanks in advance,

Angus.

How do I get evaluate() to work?

rwunderlich — Mon, 02 May 2011 04:36:01 GMT

Both

evaluate(5*8) as test1
and
evaluate('5*8') as test2

work for me. QV10 SR2. Can't test the complete script because I don't have the Definitions.xls file.

How do I get evaluate() to work?

gussfish — Mon, 02 May 2011 07:30:08 GMT

Hi Rob,

thanks for your response. I'll begin by saying I'm running QV 10 SP1 on my developer boc, which is actually ahead of what's on our production servers; consequently, I'm loathe to get even further ahead of the pack by upgrading to SP2.

So, tt seems to me that evaluate() is a rather flakey when it comes to BIFF files. Here's what I've found

Both evaluate(5*8) and evaluate('5*8'), when expressed directly in the script, per the script below, correctly return 40, BUT ONLY on the records for which RawDefinition is numeric-like i.e. 5*9, '5*9' or "5*9". Why on earth it should depend on that field entirely escapes me. This is true whether the MyFile data is loaded from inline or BIFF (both are show under later dot points).


Definitions:
LOAD
 Source As Defs.Source,
 Term AS Defs.Term,
 RawDefinition AS Defs.RawDefinition,
 evaluate(RawDefinition) AS EvaluatedDefinition,
 evaluate(5*8) As FortyFiveA, // This is fine
 evaluate('5*8') As FortyFiveB // This is fine
resident MyFile;

evaluate(inlinefield) correctly returns 40 when inlinefield contains either the string 5*8 or the string '5*8', as per when the code snippet above is preceded by the code after the next dot point.
evaluate(inlinefield) correctly incorporates variables when evaluating inlinefield values containing a dollar-expansion expression, even when the variable contains a string. However, I'm pretty sure this is because the variables are substituted at the time the LOAD * inline ... statement execute.


SET vBeginningOfHistory=1 Oct 2010;
SET vLover=Romeo;
SET vEndOfHistory=13 Mar 2194; // Never know! 😉
SET vBeloved=Juliet;
MyFile:
LOAD * INLINE [
 Source,Term, RawDefinition
 Inline,FortyFiveFromFileA, 5*8 // This is fine
 Inline,FortyFiveFromFileB, '5*8' // This is fine
 Inline,AsYouLikeIt, All the world's a stage // This is fine.
 Inline,BeginningOfHistory, My world began $(vBeginningOfHistory) // This is fine.
 Inline,EndOfHistory, The world could end $(vEndOfHistory) // This is fine.
 Inline,StarCrossedLover, $(vLover) // This is fine.
 Inline,StarCrossedBeloved, $(vBeloved) // This is fine.
 ];

evaluate(bifffield) only works when the bifffield is both unquoted and non-numeric in value. If it is quoted AND is a numeric expression, then the dequoted expression is returned. Otherwise, NULL. This is seen with replacing the above LOAD * INLINE statement with this:


MyFile:
LOAD
 'XLS' AS Source,
 Term,
 RawDefinition
from
 [Test.xls] (biff, embedded labels, table is Sheet1$);

and using this as the Test.xls!Sheet1 content:

Term	RawDefinition
FortyFiveFromFileA	5*8
FortyFiveFromFileB	'5*8'
FortyFiveFromFileC	"5*8"
AsYouLikeIt	All the world's a stage
BeginningOfHistory	My world began $(vBeginningOfHistory)
EndOfHistory	The world could end $(vEndOfHistory)
StarCrossedLover	'$(vLover)'
StarCrossedBeloved	$(vBeloved)

Of these, evaluate() for FortyFiveFromFileA returned 40, evaluate() for FortyFiveFromFileA returned 5*8 (no quotes), and NULL for all others.

You might ask, are the respective expressions from the two sources (Inline & BIFF) truly the same? As far as I can tell, yes. My test of this was to let QlikView concatenate both into the one table (MyFile) and express that table in the GUI with a Table Box. But now it got even wierder! After reloading, the Inline table's expressions 5*8 and '5*8' were now evaluat()ing to null(), while the respective BIFF entries were still 40 and 5*8 respectively! And just to be sure, I trim()ed the strings before evaluat()ing. Further, the values themselves seem identical, inasmuchas when I click on, say, '5*8' in the list, QlikView then displays a '5*8' record from both the XLS and the Inline data - yet the result of evaluate() for each differs!

So, this evaluate() function is yet a mysterious beasty insofar as BIFF OR Inline value processing is concerned, and I don't feel I can trust it unless someone can provide a logical reason for its behaviour.

How do I get evaluate() to work?

gussfish — Mon, 02 May 2011 11:03:45 GMT

Rob,

I've been able to trim one aspect of this down to a simple test case. Would you give it a try & tell me what you think?

Here's the script, including all the usual default script variables, for completeness:


SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='DD MMM YYYY';
SET TimestampFormat='YYYY-MM-DD h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

RawData:
LOAD * INLINE [
 InlineName, InlineValue
 FortyInlineRecord, 5*8
 TextInlineRecord, random text
];

Evaluations:
LOAD
 InlineName AS Name,
 evaluate(InlineValue) AS EvaluatedInlineValue,
 evaluate('5*8') As AlwaysFortyColumn
resident RawData;

My expectation is that AlwaysFortyColumn will contain a value of 40 for every record in Evaluations, without fail. In reality, when I inspect Evaluations using a Table Box displaying the three fields, I'm finding that AlwaysFortyColumn is null for Name=TextInlineRecord (though it is 40 for Name=FortyInlineRecord).

Thanks,

Angus.

How do I get evaluate() to work?

Anonymous — Mon, 02 May 2011 13:44:58 GMT

Try This:

Evaluations:
LOAD InlineName AS Name,
evaluate('5*8') As AlwaysFortyColumn
resident RawData;

Join(Evaluations)
LOAD InlineName AS Name,
evaluate(InlineValue) AS EvaluatedInlineValue
resident RawData;

How do I get evaluate() to work?

rwunderlich — Mon, 02 May 2011 20:00:33 GMT

Angus,

I think I can tell you what's happening. When an evaluate() fails, the remainder of LOAD statement in no executed for that row, So when you try to evaluate(random text) that fails, and subsequent fields never get filled. You can prove this by rearraging the order of the fields in the LOAD.

I'm not sure what the best workaround would be. Best case is to never try to evaluate bad expressions 🙂

Re: How do I get evaluate() to work?

gussfish — Fri, 13 May 2011 00:09:40 GMT

Rob,

This smells like a QlikView defect to me, especially since the documented behaviour of evaluate() is that it should merely return NULL if the expression is not valid.

What do you think?

How do I get evaluate() to work?

rwunderlich — Fri, 13 May 2011 23:43:35 GMT

I agree it's worth reporting as a bug.

-Rob

How do I get evaluate() to work?

gussfish — Mon, 16 May 2011 01:16:08 GMT

Done. Thanks for your support.

- Angus.