Search code examples
azureetlderived-columnazure-mapping-data-flow

Azure Data Factory - replace expression in derived column transformation using Mapping Data Flow

I am trying to use Azure Data Factory for a Derived column transformation task one of my tasks is mentioned below,

DESCRIPTION_TEXT : UNILEVER GROUP ##### GBR

Remove trailing country code (only when it equals to country) and ##### if they exist

My code in Expression builder:

replace(ORIGINAL_DESCRIPTION_TEXT, COUNTRY_CODE, substring(ORIGINAL_DESCRIPTION_TEXT, 0, instr(ORIGINAL_DESCRIPTION_TEXT,'#')-1))

and this is not working as I expected

(Out put is : UNILEVER GROUP ##### UNILEVER GROUP )

The Expected output is UNILEVER GROUP.

Please help me to solve this problem.

Solution

  • I have reproduced the above and able to achieve your requirement using iif in dataflow expression like below.

    iif(and(greater(instr(ORIGINAL_DESCRIPTION_TEXT,'#'),0),greater(instr(ORIGINAL_DESCRIPTION_TEXT, $country_code),0)), substring(ORIGINAL_DESCRIPTION_TEXT, 0, instr(ORIGINAL_DESCRIPTION_TEXT,'#')-1), ORIGINAL_DESCRIPTION_TEXT)

    Here country_code is a parameter and I have given the values as 'GBR'.

    If the ORIGINAL_DESCRIPTION_TEXT column contains '#' and country_codey, then it stores the string from start to the index of '#'. If not, it stores the same column without any changes.

    Result:

    enter image description here