Fetch specific column in input file and apply multiply expression in adf dataflow

Question

I have the below data(semi column(;) is the delimiter).

abc;50;7,00;def
ghi;10;20,00;jkl

If my second column value 50, then I need to fetch third column value(7,00) and multiple with -1. So my third column output would be -7,00

My final output for my input file would be below.

abc;50;-7,00;def
ghi;10;20,00;jkl

I tried the below expression but it is not working.

iif(SecondColumn == 50, (float(ThirdColumn) * -1), float(ThirdColumn))

Please help how to achieve this requirement in adf dataflows.

Thanks in advance.

Answer 1

You can use the below approach to achieve your requirement.

Your 3rd column will be taken as a string by default in dataflow when you import the projection. So, first read the column as float using projection by specifying the format.

Now, you will get the data like this in data preview.

Then, take a dataflow and use the below expression.

iif(second==50, multiply(third,-1),third)

Result in sink file:

If you don't want to change the type using projection, keep the type as string and modify the expression like below which also gives the same result.

iif(second==50, multiply(toFloat(replace(third,',','')),-1),toFloat(replace(third,',','')))