Hi All,
I am trying to create a dynamic replacement logic for a reconciliation dataset that I work on.
Essentially when I feed in the complete data, if the any of the Department/Teams which are contained in below table are required to be replaced.
E.g. If I select Level to correct as Team Name and Data to Correct as Finance, then I want Team Name - Finance Data to be replaced with Team Name - HR Data. Level to correct and Data to Correct will change and the Target Column and Replacement Value will change accordingly.
Level to correct will have one of two values (Department Name and Team Name) and Data to Correct will also have one of two values (HR and Finance).
Also, this change will only be effective till the End Date. After the End Date passes, this change will no longer be required.
I have managed to arrived at dynamic Target Column and Replacement Value columns.
I am not able to get the Expression right. The replacement is happening okay but the values which are not eligible for replacement are coming back as null.
Also, I am not sure how to make the Expression dynamic based on the changing conditions despite having tried multiple variations of IF and IIF formulas. Have not even thought about how to stop the change from happening once the End Date passes.
Any help here would be greatly appreciated.