A "misbehaving" Dynamic Replace tool / source

[Paul_s_Moody]

Hi,

I'm using a Dynamic Replace tool to replace a dummy value for SPA_M and SPA_F with the date (in the UK) that the person is entitled to receive their state pension. I'm using the attached Excel file as the source for the R anchor:

I'm having 2 different issues:

1. My expressions seem to be too complex for Alteryx to read in:

If the replacement expression is complicated, e.g. DateTimeAdd(DateTimeAdd([DOB],66,"years"),12,"months"), so adding 66 years and 1 month to the date of birth field, then it needs to be hard coded in the column [Value].

If it is linked to an underlying cell in Excel, but displaying the same result then it is read in as "00:00:00", column [Formula]. Is a "double" formula too complex for the Dynamic Replace tool to handle?

However, the expression DateTimeAdd([DOB],65,"years") seems to be fine whether read in as hard coded text or in a linked cell.

2. Not all of my data is being converted by the replace tool.

For example, if [DOB]='1946-10-01' then SPA_M should be the individuals 65th birthday - and the table works giving [SPA_M] = '2011-10-01'

However, when [DOB]='1954-03-08' then the value 2004 is generated by the table rather than [SPA_M] = '2019-09-06'

I can't see why the tool would give the incorrect result!

Thanks in advance!

[nduan]

Hi @Paul_s_Moody 

I played around with your Excel workbook and a sample workflow with a small subset of data. I made the two following changes:

1 - Within Excel, explicitly change the data type of the formulas to Text instead of Custom (only change the Type for those with an actual formula in them, eg. DatetimeAdd(DateTimeAdd([DOB],66,"years"),5,"months"), leave the other dates as is. Save the file.

2 - Within Alteryx, add a formula tool that adds quotation marks to the Formulas. I used this expression below: 

IF REGEX_Match([Formula], '\d+-\d+-\d+')
THEN "'" + [Formula] + "'"
ELSE [Formula]
ENDIF

What this formula does is look for a pattern like 2010-05-05 and then surrounds it with quotation marks. If it does not have that pattern, it leaves the row as is.

This is a pretty odd nuance for this tool, so thanks for picking up on this! I have only tested a small subset of data, though, so let us know if you run into any other issues.

[Paul_s_Moody]

Many thanks for your response!

However, I'm still having some issues with the tool.

I've made the changes you suggested to the input file, but am getting the following error:

Dynamic Replace (76) Parse Error at char(2): Parse Error

This is the same error I had previously when the complex formula were not hard coded.

I'm using Version: 2021.4.2.02731, would this cause the different behaviour of the table?

However, appending the quotes around the dates seems to sort the issue with dates "appearing" to fall through gaps.

Many thanks.

[nduan · Accepted solution]

I did run into the same error while I was testing - I realized that one of the formula values was coming in as "00:00:00" and that I missed changing the typing of one cell and got that same Parse error. 

Once I changed the cell data type in Excel from Date to Text, the workflow ran without error.  

Would you mind trying this packaged workflow and let me know if it works on your version of Designer? I am on version 2021.3.3.63061. I have packaged up the Excel workbook that you have shared with changes to the cell types.