Error in Join Recipe - computed columns

Solved!
smiley
Level 1
Error in Join Recipe - computed columns

Hi, I would like to remove the unwanted text of one of my column before the join, therefore, I have tried to create a computed column in my join recipe using the formula - substring( ), however, it throwing the error "ERROR Invalid computed column: Operator cannot be translated to SQL: substring". The same formula is allowed to use in Window/Group recipe.

May I know if this is the expected behavior of join recipe?

Example: ABC1234
Formula: substring(column, 3, 4)

How can I remove unwanted text through computed columns in Join recipe? Thanks.

0 Kudos
1 Solution
Andrey
Dataiker Alumni

Hi @smiley ,

The join recipe is implemented in a way that it uses an internal DSS SQL database. Unfortunately, substring is not one of the operations that DSS can translate into SQL query therefore the error message that you receive. Window or group recipes would give you the same error if you switch engine to a SQL database.

 

As a solution, I could propose using a prepare recipe to extract a substring and then join the resulting datasets.

 

Regards,

Andrey Avtomonov
R&D Engineer @ Dataiku

View solution in original post

0 Kudos
2 Replies
Andrey
Dataiker Alumni

Hi @smiley ,

The join recipe is implemented in a way that it uses an internal DSS SQL database. Unfortunately, substring is not one of the operations that DSS can translate into SQL query therefore the error message that you receive. Window or group recipes would give you the same error if you switch engine to a SQL database.

 

As a solution, I could propose using a prepare recipe to extract a substring and then join the resulting datasets.

 

Regards,

Andrey Avtomonov
R&D Engineer @ Dataiku
0 Kudos
smiley
Level 1
Author

@Andrey Thanks for the explanation and recommendation ๐Ÿ™‚

0 Kudos