Error in Join Recipe - computed columns

[smiley]

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.

[Andrey · Accepted solution]

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,

[smiley]

@Andrey Thanks for the explanation and recommendation 🙂