Replace incorrect data cells from second source

[Paul_s_Moody]

Hi,

I'm attaching a very simple example workflow that "works" on the sample data but is impractical for use in the real world workflow. Hopefully there is a simple-ish way of performing what I'm trying to do.

The data is substantially cut-down from the files I'm acually using (25,000+ records with 200+ columns), but the simple example shows the problem.

My main data file has a few record/column cells that contain errors. The changes file contains the replacement values. For ease they are in the same excel workbook, but in practice are stored seperately.

In the real world, the main file would remain constant, and the changes file would increase in size as corrected values are sourced; therefore the number of columns containing corrected values may also increase.

I'm looking for a simple/dynamic way of replacing the erroneous values from main with the corrected values from changes, without having to "hard-code" the changes in a formula tool and then dropping the duplicated column, again "hard coded" using a select tool...

Thanks in advance!

[BS_THE_ANALYST]

@Paul_s_Moody here's one way to do it:

The objective in the workflow was to get this:

It matches the old values with potential replacements! Once we have this, we can say, if we have a replacement, replace the current value, if not, leave the value as it was. Afterwards, we pivot the table back to how it started using Crosstab

[binuacs]

@Paul_s_Moody One way of doing this is by transposing your Changes data and join back with the main file

[Paul_s_Moody]

Hi,

Many thanks for this. The transpose, replace and cross-tab works perfectly for the sample data.

However as per issues I've seen before and had solved by the community (Solved: Re: Restore column headers and data types after Tr... - Alteryx Community) I have to add a few "hacks" in order to restore column names and data types when I use my real data.

Column names - all spaces and dashes are replaced with underscores, e.g. "Spouse - DoB" becomes "Spouse___DoB" after the tranformation process

Data types - all of the data is set to V_WString, 2048, but the starting data is a combination of dates, strings, doubles and integers...

Thanks again,

Paul