Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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
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;
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.
];
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.
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.
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;
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 🙂
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?
I agree it's worth reporting as a bug.
-Rob
Done. Thanks for your support.
- Angus.