Rename headers using dynamic rename tool

[Jaswinder]

Hi Team,

I'm an alteryx beginner. I'm facing a problem with renaming my headers. 

Consider this sample excel report layout-

			Primary Id	Owner Id	Type
Name	Account	Data Type
John Smith	Regional	A	1001	1001	Class A
Jane Doe	Global	B	1002	131	Class B
John Doe	Regional	B	1003	435	Class A

I am trying to get the header name for my data but the issues I'm facing are-

• Header names are given in 2 different rows instead of first row only. Wherever the header name in given in the second row, the respective column is blank in first row and vice versa (as seen in the sample data above)
• Not all columns are present every time and the order of columns keeps changing, due to this i can't use a text input with header names for dynamic rename as then columns get renamed incorrectly.
• If I just let dynamic rename tool name the columns on the basis of the first row then the columns with their header name in second row get renamed randomly like (Field_SomeRandomNumber) as they were blank. This random header name keeps changing everytime time i update input file with a new report so it is problematic as i need to use filter/formula tool on some of these columns and since its random and changes every time, i run into issue with target names.

Please suggest a solution.

In the attached workflow. Please look at the "Beginning Debt Check" container. I'm comparing report of 2018 with 2019. So set 1 of report is included in package and the 2nd set (to see how the reports vary) is attached externally.

Additionally if you look at how I'm importing data from excel sheets. I'm using three different inputs to import data from 3 different sheets of the same file. How can i do this in one import? I tried searching but since the structure of these 3 sheets isn't the same so most solutions i found didn't work.

[Jaswinder]

My last post got rejected for some reason, so that's why i had to repost

[danilang]

Hi @Jaswinder 

How do you want to deal with the inconsistancies?

In your Capital Reconciliation table right before the Dynamic Rename, column F22 has a entry in the 1st row(Line 1) and in the second(Ordinary Busines Income...).  What should its column header be?

Same file, column F29 doesn't have an entry in either row.

Dan

[Jaswinder]

These inconsistent line items are not required during my processing. So we can keep using the header name given in line 1 or go ahead use the one provided in second line doesn't matter. To get an idea of how i wanted these to be named, you can look at the text inputs named "CY/PY Capital Reconciliation Column Headers" given right before dynamic rename.

If you see the first select tool for each input you will get to know which columns i actually need and what names they should get from input.

My issue can be solved with just renaming F2, F3, F4 hopefully i believe

Thanks btw!

[TonyA · Accepted solution]

The simplest way to do this would be to isolate the rows with headers and concatenate them, then Union and use Dynamic rename. If there are values in both rows for a given column you can make rules about how to concatenate them. (For example, if the second row has a value use that, else concatenate). Here's a simple example.

You also might want to get rid of null columns before you process the headers.

[TonyA]

I took a look at more of the sheets. The biggest challenge I see is in the sheets where you have two rows of headers and the upper row has a value that is merged across multiple columns (for example, Columns K, L, and M in the Income Statement tab on the 2018 report). You will need to come up with rules to manage that since Alteryx can't see the formatting and there is nothing in the data to explicitly associate all the relevant lower values with the merged upper value.

[Jaswinder]

@TonyA Thanks for the solution! This is indeed simple and it works!

For the income statement reports, i took care of that using multi-field formulas.

Anyways any suggestion on how to consolidate my inputs, Part 2 of my question given in the end of original post.

[TonyA]

I don't understand the second part of your question. I'm guessing that you have the same sheets in multiple workbooks and you want to combine the same-named sheets from each workbook. There are a few good articles on ways to do things like this. Here's one example: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Read-in-Multiple-Excel-Files-with-M...

Search for something like "Input multiple sheets from excel files" for more articles.

[Jaswinder]

I'm comparing same named sheets of two excel files - 2018 report and 2019 report, for this i need to import these sheets from both files.

What I'm doing right now is having 6 input tools in my workflow - one for each sheets from 2 files. So in order to run a new report i need to update 6 input tools (with correct sheets as targets so my workflow isn't affected).

I tried importing files as list of sheets names in one input tool but since all 3 worksheet have different structure dynamic input isn't working.

[TonyA]

There are a number of ways to do this. The fastest would be to change the workflow to an app and use interface tools to specify the names of the workbooks. I've attached a packaged workflow that takes the input portion of the workflow you provided and does this. (Make sure to run it as an application.) I have two sets of Excel workbooks in the package -- you can switch between them and see the change by looking at the last column of the outputs.

This is a pretty simple approach and you could do a number of things to improve it. You could generate drop down lists of files instead of using text tools. You could convert the entire workflow to a macro and then generate a list of workbook files in the main workflow (probably using a directory tool). You might even try using file input tools. But i think the solution I'm showing here is the fastest way to get a result if you need to deliver something quickly.