Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
Hi @Kunal Bhatia ,
Would you please try case(LocalToBaseRate == 0.00000000, 1.00000000, LocalToBaseRate) . Thanks!
Expression Required for Replacing the Value (DerivedColumn) ADF
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(163.2, 54%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKB%3C/text%3E%3C/svg%3E)
Kunal Bhatia
71
Reputation points
What Expression should I use to Replace the Value From 0.00000000 to 1.00000000
In Derived Column - I tried using this Expression , but it gives me NULL result in Preview Output.
This Expression --> case(LocalToBaseRate == 0.00000000,'1')
Column Name = LocalToBaseRate
Value I want to Replace whereever it finds "0.00000000" , it should replace to "1.00000000"
Please suggest the expression for Derived column .
Thanks
Azure Data Factory
Answer accepted by question author
Nasreen Akter
10,896
Reputation points
Volunteer Moderator
-
Kunal Bhatia 71 Reputation points
2022-04-28T20:06:07.803+00:00 This gives me error, below is the screen shot
-
Nasreen Akter 10,896 Reputation points Volunteer Moderator
2022-04-28T21:31:07.893+00:00 Hi @Kunal Bhatia , what is the datatype for LocalToBaseRate field?
-
-
Nasreen Akter 10,896 Reputation points Volunteer Moderator
2022-04-29T13:27:08.953+00:00 @Kunal Bhatia , in this case you can either convert the LocalToBaseRate to float
toFloat(value, format, locale)or convert the 0 and 1 to decimaltoDecimal(value, precision, scale, format, locale). Thanks! -
Kunal Bhatia 71 Reputation points
2022-04-29T13:39:06.92+00:00 Ok, But I am not sure about the correct expression for the whole solution.
Can you Please suggest me the correct expression for this ...
case(LocalToBaseRate == 0.00000000, 1.00000000, LocalToBaseRate)
toFloat(value, format, locale) ... ?
How to write the whole expression in derived coloumn -- Is this the expression
case(LocalToBaseRate == 0.00000000, 1.00000000, toFloat(1)) ...? -
Nasreen Akter 10,896 Reputation points Volunteer Moderator
2022-04-29T15:35:10.437+00:00 Hi @Kunal Bhatia ,
You can try something like
case(LocalToBaseRate == toDecimal(0, 18,8), toDecimal(1, 18, 8), toDecimal(LocalToBaseRate, 18,8))where 18 is theprecisionand 8 is thescale. Please change the values accordingly. Thank you! -
Kunal Bhatia 71 Reputation points
2022-04-29T16:39:45.373+00:00 @Nasreen Akter -- It works this time ... Thank you very much for the help , I appreciated.
Just one thing what is this Precision means (18) ,, Is it the Total count of word " toDecimal(0, 18,8) " ?
-
Nasreen Akter 10,896 Reputation points Volunteer Moderator
2022-04-29T16:42:53.207+00:00 decimal(10,2) means you can have a decimal number with a maximal total precision of 10 digits. 2 of them after the decimal point and 8 before. (10,2) defines the precision of the decimal value.
-
Nasreen Akter 10,896 Reputation points Volunteer Moderator
2022-04-29T16:46:37.927+00:00 @Kunal Bhatia : for Decimal(18,8), maximal total precision would be 18. 8 of them would be after the decimal point and 10 before. Thanks!
-
Kunal Bhatia 71 Reputation points
2022-04-29T16:47:01.28+00:00 Ok got it ,, Thank You , Appreciate it.
-
-
ShaikMaheer-MSFT 38,631 Reputation points Microsoft Employee Moderator2022-04-29T16:56:15.52+00:00 Hi @Kunal Bhatia , As this helped you. Kindly consider marking answer as
accepted answer. Accepted answers help community as well. Thank you. -
Sign in to comment