Harmonising Fields after Batch Macro Union

[jay_hl]

I have inputted a number of excel files through a batch macro. They all have slightly different schemas (field names) so the macro is set to union the results on output. 

This then ends up with multiple different fields for what is in reality the same field (eg Name, Names, FullName, Fullname) where lots of the rows are blank depending on what was in the source data.

Is there a quick and easy way to harmonise these to the same column?  My natural instinct would be to write a formula like the below:

If Name is not null, then Name, elseif Names is not null then Names elseif FullName is not null then FullName etc.

Given there are approximately 10 “real fields” across 30 variations I wondered if there was a best practice to tackle this and align all the data to a single column easier than nested ElseIfs

Jay

[pedrodrfaria]

Hi @jay_hl 

You have different ways to approach this.

If they are always the same file structure, you can use the Union tool to manually assign everything to the correct place.

If you know that columns will always say Quantity or Qty or Quant or QTYs, etc. you can use the Dynamic Rename and create a If Contains logic to change the name to a single name throughout all the tables.

You can also do a logic with the Dynamic Rename, Select and the interface Dropdown to dynamically select the column that represents that specific column, and use the dynamic rename and the select to rename it to the proper name and the select to only select that column.

Please let us know which way works best for you and then we can better support you.

Pedro.

[jay_hl]

I just updated my original post.

I realised that the obvious solution for figures (like Quantity in the original post) is to sum it all to a new column, but the majority of the field types are strings (which I can’t sum!)

[pedrodrfaria]

Hi @jay_hl 

What I mentioned before still applicable to the fields being a string value.

[jay_hl]

Thanks for the speedy reply Pedro.

Does Dynamic Rename allow the resulting logic to output the same field name multiple times. If I renamed Name, Names, Fullname all to be “Full Name” that would be 3 fields with the same (which I didn’t think was allowed)?

Then how would the three separate fields merge without writing nested Ifs in a formula tool?

Jay

[pedrodrfaria]

If these fields are coming from different files, you do this logic before the union, and then the union will consider those fields.

If they are on the same file, then we would need to do a Transpose, Formula, Crosstab logic. To transpose it vertically, assign the same names to the rows of data and then crosstab it all together.

Pedro.

[pedrodrfaria]

I attached an example with the second logic I mentioned.

Before:

After:

Pedro.

[SeanAdams]

Hey @jay_hl 

What you could do is to do a transpose (to get columns into rows), then a lookup / find & replace (using a static translation table in the text input tool; into a Find-replace) to change the field names; and then use Crosstab to re-explode them out to columns.