How to write expression for not null with case statement in ADF data flow

ARUP DEBBURMAN 21 Reputation points
2021-06-01T16:57:09.78+00:00

Hi Team,
Could you please help me to write the below expression in ADF data flow

CASE
WHEN GUPPLIERCODE IS NOT NULL AND GUPPLIERCODE LIKE '%_%' ESCAPE '\' THEN SUBSTR(GUPPLIERCODE,1,INSTR(GUPPLIERCODE,'_')-1)
WHEN GUPPLIERCODE IS NOT NULL THEN GUPPLIERCODE
ELSE ' '
END

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,253 questions
{count} votes

Accepted answer
  1. KranthiPakala-MSFT 46,602 Reputation points Microsoft Employee
    2021-06-02T19:06:32.067+00:00

    Hi @ARUP DEBBURMAN ,

    Sorry for the delayed response and thanks much for sharing additional information.

    You can use below expression in your derived column expression builder:

    case (   
         
       (isNull(GUPPLIERCODE) || (upper(GUPPLIERCODE) == 'NULL') || GUPPLIERCODE == '0') , '',  
       and(not(isNull(GUPPLIERCODE) || (upper(GUPPLIERCODE) == 'NULL')), like(GUPPLIERCODE, '%_%')) , split(GUPPLIERCODE, '_')[1] ,  
         GUPPLIERCODE   
    )  
    

    Please see below test results (You can further modify this expression based on your case statements )

    101805-image.png

    Hope this helps. Do let us know if you have further query.

    ----------

    Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.