Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Global Variable in Calculatr

Hi, we're running Matillion ETL for Redshift
Version: 1.24.6 (build 55) and I'm trying to use a variable in the expression box of a Calculator component. i defined a simple global numeric variable called test with a value of 3.

However when i attempt to reference the variable in the expression box: ${test}
it keeps generating the following error:
Check properties for unmatched parenthesis, quotes or illegal characters

What am I doing wrong ?
thanks,
jm

3 Community Answers

Matillion Agent  

Kalyan Arangam —

Hi Jay

Please can you add a screenshot of the complete expression with the error?

Thanks
Kalyan


Kristin Hybertsen —

Hi! I have the same problem for text and date variables. Numeric works fine.
Here is my solution:
date[time]: cast('${date_variable}' as date[time])
text: '${text_variable}'


Matillion Agent  

Ian Funnell —

Hi Jay,

Variables are added in the Calculator component (and in other places) using simple string substitution of the variable’s default value for the environment you’re using.

So if you have a Numeric variable with default value 3, it will generate

SELECT 3 as "your_expression_name"

If you have a Text variable with default value x, it will generate

SELECT x as "your_expression_name"

As Kristin pointed out, Redshift will interpret this as a column name, called x, which probably doesn’t exist, and so will fail. You need to wrap the variable in single quotes in the Calculator expression to make Redshift interpret it as a string literal.
Same thing for dates and times: you can use a CAST in the Calculator as Kristin showed, or use Redshift’s TO_DATE SQL function, which allows you to be explicit about the format mask.

Best regards,
Ian

Post Your Community Answer

To add an answer please login