Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gussfish
Creator II
Creator II

How do I get evaluate() to work?

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.

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I agree it's worth reporting as a bug.

-Rob

View solution in original post

8 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

gussfish
Creator II
Creator II
Author

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:

TermRawDefinition
FortyFiveFromFileA5*8
FortyFiveFromFileB'5*8'
FortyFiveFromFileC"5*8"
AsYouLikeItAll the world's a stage
BeginningOfHistoryMy world began $(vBeginningOfHistory)
EndOfHistoryThe 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.

gussfish
Creator II
Creator II
Author

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.

Anonymous
Not applicable

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;

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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 🙂

gussfish
Creator II
Creator II
Author

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?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I agree it's worth reporting as a bug.

-Rob

gussfish
Creator II
Creator II
Author

Done.  Thanks for your support.

- Angus.