Need help

Question

Hi There,

How can I create new column basis on my input column? Below is my input column and i want to create expected output column using formula tool. Please help me with formula.

I did try below formula but didn't work as expected.

if Contains([Name], "1") then "1Y" ELSEIF Contains([Name], "2") then "2Y" ELSEIF Contains([Name], "3") then "3Y" ELSEIF Contains([Name], "4") then "4Y" ELSEIF Contains([Name], "5") then "5Y" ELSEIF Contains([Name], "7") then "7Y" ELSEIF Contains([Name], "10") then "1 Y" ELSE [Name] ENDIF

InputExpected Output1 Yr To 2023-12-311Y2 Yrs* To 2023-12-312Y3 Yrs* To 2023-12-313Y4 Yrs* To 2023-12-314Y5 Yrs* To 2023-12-315Y10 Yrs* To 2023-12-3110Y1 Yr To 2014-12-311Y1 Yr To 2015-12-311Y1 Yr To 2016-12-311Y1 Yr To 2017-12-311Y1 Yr To 2018-12-311Y1 Yr To 2019-12-311Y1 Yr To 2020-12-311Y1 Yr To 2021-12-311Y1 Yr To 2022-12-311Y1 Yr To 2023-12-31_21Y

DataNath · Accepted Answer

Hey @anonymous008_G, this isn't working/you'll just get '1Y' for all of the results as the if checks work sequentially i.e. will check for 1 first, then 2 if that's not satisfied etc. - In your case, you have a 1 in all of your records because of the dates and so it stops at that first check.

If you want to just take the number of years at the very start and add a 'Y' which it looks like you're trying to do, you can do so pretty simply with a Formula expression such as:

GetWord([Name], 0)+'Y'

DataNath · Accepted Answer

No problem @anonymous008_G - always happy to help! With the date requirement, there's a few ways you could go about this and REGEX_Replace() is definitely one. If the string format is fairly consistent/won't have anything else that holds an ISO date-like pattern then you could just add an expression like this:

REGEX_Replace([Name], '.*(\d{4}-\d{2}-\d{2}).*', '$1')