Multiple Field Find and Replace

Question

I am making an app inn which a user can load a product file, select specific attributes of that product, and replace it in all locations of our internal data.  There are two challenges.  First, I cannot transpose the internal data to join on the selected fields as it will create billions of records.  Second, I need to keep the order of the fields the same.  So, ideally I would find the data from the user file and replace the data in the internal data for every field while keeping structure intact.

I can use a join to connect the two and get the new values.  If it was manual, I could then rename the new fields and move them over to where the originals were, but I need to automate that rename and movement in the app.

I'm including an image of sample data.  Imagine the user uploaded a file with the new Height, Width, and Depth fields and wanted to replace the data in the image.  The real data would have about 300 fields and 100,000's of rows.

Thanks,

Robert

Claje · Accepted Answer

Hi,

I've attached a workflow which takes advantage of the CReW macro "Dynamic Formula".  We read in the source Data and use it with a Sample tool to build a list of all fields that have to be included.  We then union that to the user input, which can contain any number of columns, and join the two on the "Key" value.

We then write a dynamic formula to compare the "Right_" fields (which come from the user input), and if they are not null, we replace the values from the data source.  We then deselect all "Right_" columns from the dataset.

This may still take some time to run for 300 columns, but I've used approaches like this for similar datasets, and it is usually pretty quick.