This post will describe how do you use CASE WHEN statement in Azure data factory(ADF). If you are coming from SSIS background, you know a piece of SQL statement will do the task. However let's see how you do it in SSIS and the very same thing can be achieved in ADF.
Problem statement:
For my simple scenario, In case PortfolioTypeCode is either 'Mutual Fund' or 'Pooled Fund' it should return 1 Else it should return 0.
How do you do in SSIS?
In SSIS, under data flow you will have OLEDB source like below fig 1:
And open the OLEDB source and then Write SQL command like below and you are done:
SELECT Col1,
CASE WHEN PortfolioCode IN('Mutual fund','Pooled fund')
THEN 1
ELSE 0
END IsFund,
Col2
From Table1
How do you implement in ADF?
However in ADF, to achieve the same you need to use Expressions. ADF have very same concept of data flow like SSIS. In the data flow, after the source dataset is established you can add 'Derived Column' activity like below Fig 3:
Now you can give a new column name and then add the expression (Fig 4):
Let's see how Case expression works: it takes 3 arguments, those are condition, true and false. However, it can have alternating condition which describe in the figure 5:
For my simple scenario, If PortfolioTypeCode is either 'Mutual Fund' or 'Pooled Fund' it should return 1 Else it should return 0.
Since you can't have CASE WHEN rather using case as Expression, the code will look like below:
case( PortfolioTypeCode=='Mutual Fund',1,
PortfolioTypeCode=='Pooled Fund',1,0)