Rename Fields for Multiple Tabs of Different Schema

I have one file containing data in multiple tabs with different schema (file- NameAgePosition) and another file containing the desired names for the fields in 1st file (file-MapFields). Looking for the way to rename the fields in 1st file with the 'ReqdFiledName' data in 2nd file.? The files are attached. Thanking in advance for any suggestion towards the solution.

NameAgePosition.xlsx

MapFields.xlsx

Batch Macro

Accepted answers

ipokharel

Thank you @Thiagoros , @apathetichell and @grazitti_sapna ! I made few changes to the macro and was able to get to the solution without Transpose/Cross Tab. Workflow is attached. Let me know if you see any issue here.

RenameFieldsMultipleTabsDifferentSchema.yxzp

All comments

apathetichell

dynamic rename - with the right anchor configured for the field rename mapping.

apathetichell

but if you have multiple fields which would map (ie every row has an age1, age2, age3... ageN) - this will not work. Assuming only one value exists - your best option is to:

1) recordid

2) transpose (with recordid as a keyfield)

3) drop the trailing digits from your [Name] field - use a formula tool to do something nifty life regex_replace([Name],"^(.*)(\d+)$","$1")

4) use a summarize tool group by recordid/name - max value - this will normalize your naming syntax and take only the fields with values.

5) Cross-Tab (keyfield record id), column names - name/ value - max_value, concat.

Note - you can skip 4 and use Cross-Tab instead of take the max value - but you won't have an intermediate step to troubleshoot if anything goes wrong...

ipokharel

Thanks @apathetichell but that did not work for me as the challenge is to rename just the column headers with desired names in MapFields file, and I will have more than 50 tabs 'NameAgePostion' file. So I believe this will need a Batch Macro. Please let me know if you have any workflow with Batch Macro to address the similar case.

apathetichell

@Thiagoros - no. this has nothing to do with pandas.

@ipokharel - didn't realize that you had a situation with multiple sheets (hadn't looked at your inputs - just read your case). You do need a batch macro to read in the file. I would not recommend using a match though because if you have 50 different naming conventions it's a waste to have that matching syntax. Instead - do what I suggested (with the addition of the batch macro). Worfklow attached.

The alternative is to have a dynamic rename in your batch macro set up with rename field via formula and use a variation of the regex above. Use the macro I attach. add a Dynamic rename before macro output. The Dyanmic Rename is configured for all fields (including dynamic/unknown). It is set to formula mode. You use the following formula:

REGEX_Replace([_CurrentField_],"^(.*)(\d+$)","$1")

and then you save it. The key is how much troubleshooting you thing you'll have to do.

The underlying issue is Alteryx (or Python)has to know that you want the name..., age... and position... columns to end up as one value cross records. otherwise a simple dynamic rename/mapping will just change the first column names and replicate other column names (at n-1) because Alteryx knows they are different columns but doesn't have a DISTINCT name for them. Pandas would also be unhappy here.

fieldmapping.yxzp

grazitti_sapna

Hi@ipokharel

I have made Workflow for your Problem. Please Check.

If it works Kindly accept it as Solution.

Thanks

Rename Fields for Multiple Tabs.yxzp

ipokharel

RenameFieldsMultipleTabsDifferentSchema.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703