Hi Alteryx Community I have a doozie.
Please refer to the attached spreadsheet. I have 135 reports with the layout found on the 'Input Tab' and I would like to merge & create a workflow that will output the data as on the 'Preferred Output' tab for further analysis.
I'm not (yet!) concerned about looping through the directory to input the 135 spreadsheets as I have a workflow that does something similar, but I'm not even sure where to begin with this one. So far I have a an Input Data, a Sample Tool & a Text to Columns tool :)
Notes
1) all 135 reports are .xls files
2) the aggregates and each report are based on the Plan column in the 'Preferred Output' tab
Recommendations/assistance/ways to tackle this would be greatly appreciated.
Thanks,
Sue
Solved! Go to Solution.
Whew, @YeahMan, that is a doozie, but a perfect use case for Alteryx!
Here are some thoughts on how to start breaking this down:
Wow @kelly_gilbert thank you very much for taking the time to answer my question with so much detail!
To answer some of your questions:
I was going to start small (one report) then worry about the directory load after. I have a workflow with a macro that does exactly this for, oddly enough, 135 XML files ;). I will look at the Directory/Dynamic input tools though, especially the Cache and Run workflow option!
Yes the output column headers will be the same for all 135 XLS reports, and the input column headers are all the same except for the merged cell H & J, currently labeled 'P0101'. The 'P0101' represents the Plan under consideration. There is one report for each plan and all the data in that report pertains to the plan. One of things I need to do is copy the 'P0101' into the Plan column for all rows in the current report. So, I can either get it from the merged cell H&J or parse it from the file name - which would probably be easier as the plan name is included as well. E.g. KPI SUMMARY - GRAD ONLY~P0101~DENTAL ASSISTING (LEVELS I AND II).xls
Again thank you,
--sue