Dynamic Replace - Am I trying to be too clever?

[Paul_s_Moody]

I'm using a dynamic replace table to change column [GMP Revaluation Rate] within a large data set.

The majority of records are [null] within the data, except for a couple with a correct value.

I want to use the following table to update the [null] values to the correct numeric value based on [Date of leaving], but to leave any existing data alone.

A previous draft table didn't have the top row and all values were replaced, including the ones that were not null.

I thought adding the top row in the table to "ignore" any rows that are not [null], but I get a "Parse Error at char(0): Unknown variable GMP" message from my workflow.

Have a made a mistake in the syntax in the dynamic replace table, or am I trying to do too much in a single step?

I've also attached a snip of the configuration settings for the tool.

If it can't be done in a single step, what steps should I be taking?

[kathleenmonks]

Hi @Paul_s_Moody 

Can you deselect the box for "Values are Expressions/Formulas" and rerun your workflow? Let me know if this fixes the problem.

[Paul_s_Moody]

Hi,

Thanks for your reply.

I've unchecked the "Values are Expressions/Formulas" box as you suggested.

The workflow runs without errors, but the cell now contains the string "GMP Revaluation Rate" rather than the numerical value it used to hold.

I've also tried putting the table entry to [GMP Revaluation Rate], but the cell is then populated with "[GMP Revaluation Rate]".

Regards,

Paul

[kathleenmonks]

Hi @Paul_s_Moody 

It looks like the first expression in row 1 is actually checking if there is a value at all and replacing it with "GMP Revaluation Rate." I am guessing you want that expression to read isNull() instead of !isNull(). I would recommend removing the exclamation point and rerunning your workflow.

Let me know if this solves your problem!

[Paul_s_Moody]

Hi,

I think I do need the !IsNull(). The 1st row is trying to say "leave alone if a value exists". The other rows then should populate the column if one doesn't.

I've attached an additional data set (cut down from the full set of policy data so only the relevant columns are left) as I'm not sure I've explained things properly.

In the current column GMP Revaluation Rate, most fields are blank except for 1 of them.

When I run the Dynamic Replace, I would like all of the [null] cells to be replaced with the values in the table, but for the row that already contains a value, this needs to remain unchanged (this is an override as the value is different to that generated by the table based on the date).

Hence my logic is supposed to be:

IF null() THEN

     use the [date of leaving] column to find an appropriate Rate from the table 

ELSE 

   leave the Rate in the data unchanged as it's already in the data 

END IF

Does this make more sense?

[kathleenmonks · Accepted solution]

Hi @Paul_s_Moody 

Ah! I see now. Ok so in order to get this to work, you need to add a DateTime parse tool before the dynamic replace. This will allow the formula to operate as you would expect as right now it is comparing a string value to a date and thus, you are getting odd results. Additionally, in order to fill in the existing GMP revaluation rate, I would recheck the "Values are expressions/formulas" box, change row 1 to [GMP Revaluation Rate] and change the other rows to have quotes around the values so it views them as expressions.

I've attached an example workflow to show you exactly what to do. I would also recommend adding a formula tool to change the existing rates to the same % format as the new ones. 

[Paul_s_Moody]

Thank you so much.

This now works perfectly!