Multiple Worksheet in Same file with Different Schema

[davidlocke]

I have a worksheet with around 60 tabs (will vary during the years) that lists various cost centers employees survey of time spent working on R&D activities. The end goal of this workflow is two fold.  I need to calculate the number of qualified wages by employee and by cost center; and I need to calculate the number of qualified wages by project.  While every survey will be be the same number of columns the number of rows will differ.  Additionally, 9 of the last column headings will differ (last column will not differ).

Below is a screenshot of the area of the survey that will determine the number of qualified wages by employee and the percent of time by employee that was spent on the various projects.  The column headers from A to AO will not change and the data will always start on row 58.  The difference in schema only changes in columns AP to AX as each cost center will be working on different projects and will enter the projects they are working on row 58 from columns AP to AX.

I have looked through several of the community postings around dynamic inputs, inputting on multiple tabs with the same schema, and inputting multiple tabs and multiple files with different schema (each schema within each file is the same).  I haven't found something that works for my use case.  My brain goes to somehow using an input tool on multiple tabs and each instance of a new project it creates a new column.  Once I get all the data in, the other tricky part is after I create the workflow to calculate the total qualified wages by employee is layering this into a calculation that distributes the wages amongst the projects and assigns the qualified wage amounts combined with the percentages for projects 1-8 to determine the appropriate amount of wages to apply to each qualified project.

I've attached a sanitized snippet of my data.

[DanM]

@davidlocke 

To help get you on your way I can give you some suggestions to look into.

There's a couple of ways to handle this but the one I'll suggest is using the Input Tool and the sheet name option. This will give you the names of the 60 tabs. You can then feed these sheet names into the Dynamic input tool to update the sheet of the template you select. If the schema is truly the same it should bring in all of your data. https://community.alteryx.com/t5/Interactive-Lessons/Connecting-to-Multiple-Sheets-at-Once/ta-p/8287...

If the schema isn't the same across the tabs, you'll need to create a simple batch macro using the Input tool and use the auto config position by position in the interface designer. https://community.alteryx.com/t5/Interactive-Lessons/Creating-a-Batch-Macro/ta-p/657923

In regards to the second question. It may be easiest to Transpose the columns AP-AX. This would then put all that data into one field. This way you won't have to worry about whether or not those columns are different. https://community.alteryx.com/t5/Interactive-Lessons/Changing-Data-Layouts/ta-p/82876 You can then use the Cross tab tool to put the data back to its original setup once you have completed the calculations.

Hope that gets you on your way. The interactive lessons provided should help you understand the tools suggested.

DanM

[davidlocke]

@DanM 

Thanks for your suggestions.  Prior to posting here, I started with the the Input Tool and sheet name with a dynamic input tool.  It doesn't like it when the columns AP-AX are different.  I tried using some other posts samples as a guide on the batch macro's, but it wasn't very helpful.   I did all the integrated trainings when I started, but that was a couple years ago.  So I'll review it again to see if there is material with the knowledge I have know that will make it more applicable.

As a note, I already have a workflow that calculates the qualified wages and project percentages from last year, so that part of the workflow shouldn't be terrible.  However, last year we tried using a SharePoint approach and used a Big4 accounting firm to assist, which while being a great learning experience ended up not meeting our needs.  However, on the Alteryx side of things, the data coming in was very similar except for the projects.  Instead of each project having it's own column,  their was one column for project name and one column for percentage work on said project, so each project an employee worked on required a new time survey row.  Without getting into narrative, this was a mess.  So what I'm trying to do is minimize timesheet user difficulty while hopefully utilizing the old model as much as possible.  To do so, I basically will need to hopefully get one of the batch processes working and find a way to get projects and project percentages to transpose, which would allow me to use the old model for all of the calculation/outputs.

What I perceive as being the biggest issue is knowing how and when to transpose the projects.  Will it need to be layered into the batch macro some way or with the batch macro will I be able to bring in all of the projects columns from all of the tabs including their related percentages and then transpose (which is easy enough).

[davidlocke]

While I would not be comfortable submitting last year's workflow model for the general public to see, I could send it directly to someone if that is helpful, but I would need to do some sanitizing first.

[apathetichell]

rename your columns to an excel syntax (A-ZZZZ) and bring in your first row as data.  use dynamic-renames in macro to change the headings as needed.

[DanM · Accepted solution]

@davidlocke 

I attached a sample of the Input Macro. If you open the macro up and open the Interface Designer you will be able to toggle from name and position with how the data is brought in. It should ignore your schema and just bring in by position. You can also update the Input tool in the macro to ignore your headers as another schema issue solver. If you use that option you can use the Dynamic Rename tool to pull your headers up from the first row.

I would bring in all of your data and make sure it looks good before you do the transpose.

[davidlocke]

@DanM 

So I have it now where I can bring in all the data by bringing in the headers as data.  I did the dynamic rename.  Now I'm on the transpose.

[apathetichell]

A few things:

1) Macro is designed for .xlsx files - not .xlsm files.

2) Macro is not set to dynamically change the directory - only the file name. This can be fixed by changing the properties in the action tool inside the macro.

3) Macro assumes all files have one sheet that you want (UGLs)

Question are:

1) are you using .xlsx or .xlsm - or both.

2) are these multiple sheet or single sheet files.