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

Apply function on variable in calculator

Hi All!
I do option 1 here in this example: https://bigquerysupport.matillion.com/customer/portal/articles/2951841?b_id=8914

I'm trying to create yet another column from the same variable and thus applying a string function on the variable in the calculator component. But it doesn't seem to work. Should I store the variable in a table first and the do another calculation?

Br
Cris

7 Community Answers

Matillion Agent  

Damian Chan —

Hello Cris,

Just to help me understand what you’re trying to do. So following option 1, you were able to create a new column containing filenames as your values, correct? and now you’re trying to work with that filename column and create a new column from that?

If so you can try using the Window Calculation component. More information is provided below.

https://bigquerysupport.matillion.com/customer/portal/articles/1991935?b_id=8914

Best Regards,
Damian


Cristian Ivanoff —

Hi Damian,
I create a new column as in option 1. At the same time I would like to create yet another column (in the same component) using the same variable that holds the filename. I'm trying to apply a string function to it but it doesn't work. So, I suspect, that the string functions only can be applied to fields or am I doing something wrong?
This what I do with the variable: 'REGEXP_EXTRACT(${gs_filename}, "\\d{4}-\\d{2}-\\d{2}"'


Matillion Agent  

Damian Chan —

Hello Cris,

Ok, I see. You’re trying to extract the date from the filename and create a new column to hold the values? I would assume that what you have above should work. Try changing your REGEX pattern to the one below.

\d{4}-\d{2}-\d{2}

See if that makes a difference.

Best Regards,
Damian


Cristian Ivanoff —

If I use a single \ I get this error:
Syntax error: Illegal escape sequence: \d at [1:27]

Br
Cris


Cristian Ivanoff —

This works fine in BQ - that's why I suspect I can only apply functions to fields.

SELECT REGEXP_EXTRACT(Filename, "\\d{4}-\\d{2}-\\d{2}")
FROM `sourcetable`


Cristian Ivanoff —

Hi again,
I solved it by manipulating the variable in a python script. I created an empty "gs_file_date" and did this:

import re
regex = re.compile('\d{4}-\d{2}-\d{2}')
tofiledate = regex.findall(gs_filename)
context.updateVariable('gs_file_date',tofiledate[0])
print (gs_file_date)

This will populate the "gs_file_date" variable with the date and the I pass the variable to the transformation job to add it to the table as in option 1 above.

Br
Cris


Matillion Agent  

Damian Chan —

Hello Cris,

Right. Ok sorry for the late reply. I understand what you mean now.

So what you will need to do is use the Calculator component to create a column for your filename and assuming you’re storing the whole filename you can then attach a second Calculator component to the first one in order to create your second column referencing the filename column you’ve created in the first Calculator component. I hope that helps. So you don’t have to store it first but you’ll need to declare it and use then can use it in the following components that you attach to it.

Best Regards,
Damian

Post Your Community Answer

To add an answer please login