I am tryin to use a variable in the join condition of the target update component in Matillion. The value for the variable has already been set in the orchestration job. When i run the job the variable is not getting resolved

Matillion ETL
1

I am tryin to use a variable in the join condition of the target update component in Matillion. The value for the variable has already been set in the orchestration job. When i run the job the variable is not getting resolved. The join condition looks like : ${variable_name}

Not sure if this needs to be used in some other different way? Can someone suggest.

2

Just to avoid any confusion the target update component is part of the transformation job.

3

Hi @pY471040 (Customer)​

We have used variables in the Table Update component as the screenshot below illustrates. Are you passing the variable from the parent process correctly? Could you post a screenshot of the validation error when running if possible?

Thanks!

0694G00000CW7x9QAD_0D74G000006YvQqSAK
0694G00000CW7x9QAD_0D74G000006YvQqSAK737×383 21.3 KB

4

Hi @ELTuser77​ ,

I'm trying to use the same approach for one of the use cases. Can you please point me how did you construct the join statement dynamically?

Thanks!

5

Hi @SK622554​

You need to build a grid variable of the key(s) which will form the join condition.

We use a python script to read that grid variable to create a SQL statement.

An example of the join statement is:

"target"."ID" = "input"."ID"

where the aliases are hopefully self explanatory.

Something like this python below would help you get started - this created the join_statement job variable in the previous screenshot I posted:

# use target as the target table alias and input as the staging table alias

statements = ['"target"."{y}" = "input"."{y}"'.format(y=x.upper()) for x in key_cols]

statement = "AND ".join(statements)

print( "Created the join statement:\n{statement}".format( statement=statement ) )

context.updateVariable('join_statement',statement)

the key_cols grid variable contains a single column, called column_name.

Thanks!

6

Thank you so much @ELTuser77​