Error in Join Recipe - computed columns

smiley
smiley Registered Posts: 6 ✭✭✭✭

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.

Best Answer

  • Andrey
    Andrey Dataiker Alumni Posts: 119 ✭✭✭✭✭✭✭
    Answer ✓

    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,

Answers

Setup Info
    Tags
      Help me…