In Data Prep - Customizing Replace By Position
Hello,
Enjoying the new features in Monarch V14. Replace by position feature is great. I wondered if anyone knew of a way to combine replacing a value in a certain position IF it equals a certain value?
So for example, replace position 5 if it equals 7:
12345
24357 ===REPLACE THIS ONE
43167 ====REPLACE THIS ONE
54321
31563
All suggestions welcome.
Thanks
Answers
-
Greetings!
The only way I can think to do this is with a calculated field.
There is a function called Substr() that will check position 5 (or whatever position you need) in the string, and another function called Stuff() which allows you to replace only what's in a specified position. The total formula would look like this:
If( Substr([Employee ID],5,1) = "7", Stuff([Employee ID],5,1,"Replacement String"), [Employee ID] )
Keep in mind, these functions only work on strings, so if that field is numeric, you will need to convert it to text prior to entering this formula.
If you need more explanation, let me know!
-Stephen
0 -
Altair Forum User said:
Greetings!
The only way I can think to do this is with a calculated field.
There is a function called Substr() that will check position 5 (or whatever position you need) in the string, and another function called Stuff() which allows you to replace only what's in a specified position. The total formula would look like this:
If( Substr([Employee ID],5,1) = "7", Stuff([Employee ID],5,1,"Replacement String"), [Employee ID] )
Keep in mind, these functions only work on strings, so if that field is numeric, you will need to convert it to text prior to entering this formula.
If you need more explanation, let me know!
-Stephen
Okay, then calculated field it is. thanks Stephen.
0
Altair Employee