ADF Understanding the Case Statement-CodePudding

Given the following Derived column Expression

case(Rolling =='A'||Rolling == 'B'||Rolling == 'C'|| Rolling =="S"
     , "
     , case(Alpha== 'EE'
         , toString(toDate(Manu_Date, 'yyyy-MM-dd'))
         , case(Alpha=='CW', Del_Date,"))
)

2 questions

Is there a Better way to write this code?
What is this code trying to do ?

I am trying to understand what they are trying to achieve with this expression?

CodePudding user response：

In the given expression, after Rolling=="S", it is not double Quotes ". It should be two single quotes''
Similarly, after Del_date, also it should be two single Quotes.

case(Rolling =='A'||Rolling ==  'B'||Rolling ==  'C'|| Rolling =="S", '',
case(Alpha==  'EE', toString(toDate(Manu_Date, 'yyyy-MM-dd')),
case(Alpha=='CW', Del_Date,'' )))

What is this code trying to do ?

Syntax for case statement is case(condition,true_expression,false_expression)
Initially, this expression checks if Rolling is 'A' or 'B' or 'C' or 'S' and then assign the value as '' (empty string) for the derived column.
When the above condition is false, then checks if Alpha is 'EE' and assign the value of Manu_Date in string format.
When the second condition also fails, it checks if Alpha='CW' and assign the value of Del_Date column.
When all the above conditions are not met, '' (empty string) is assigned. This will be the default value.
I repro'd this with sample input. img1: input data
In derived column transformation, new column is added, and the expression is given as in below script.

case(Rolling =='A'||Rolling ==  'B'||Rolling ==  'C'|| Rolling =="S", '',
case(Alpha==  'EE', toString(toDate(Manu_Date, 'yyyy-MM-dd')),
case(Alpha=='CW', Del_Date,'' )))

enter image description here img2: Derived column transformation output

Is there a Better way to write this code?

Since the order of condition is important to assign the values to the new column, case statement is better way to do.
But, instead of using nested case statements, we can use single case statement to achieve the same. Syntax: case( condition_1, expression_1, condition_2, expression_2,.......... condition_n,expression_n,default_expression). Null will be the default value, when the default expression is omitted.

Modified expression

case(Rolling =='A'||Rolling ==  'B'||Rolling ==  'C'|| Rolling =="S", '',
Alpha==  'EE', toString(toDate(Manu_Date, 'yyyy-MM-dd')),
Alpha=='CW', Del_Date,'' )

enter image description here img 3: Results of both case statements

Both the expressions are added in the derived column transformation and results are same in both cases.