Greeting everyone,
I've tried to create a work flow to generate a complicated spreadsheet by using existing data. However, I couldn't work out the logic and struggle to use the right method to solve this issue.
I have a original data.xlsx
I need to generate a spreadsheet as template.xlsx
Thus, I need to create new columns of "Bus#", "kV", "Load ID", "District" and "each year's % of CityA Total MW" in original data. For Load ID, I need to leave those Subs without IDs as blank.
For each year's MW value, I need to import the values from original data spreadsheet and also divide each value by 1000. For Sub 4, 5, 6 and 7, I need to add their MW value with Sub 3 until 2021. After 2021, I only need to add Sub 4, 5 and 7 with Sub 3's MW value, Sub 6 leaves it as its own.
For each year's percentage value, I need to calculate a Total MW value of all the subs first for each year (create a new row to generate this value) and then use each sub's MW value divide by Total MW value to get percentage value.
Any suggestions or advise that I could create the work flow? Thanks for the help in advance.
Hi @esun1028 ,
I've developed a workflow that gets you most of the way there.
Here's a quick run-through:
Step 1 - Read in the data and structure it in a clean way we can work with
Step 2 - Calculate MW and % of Total MW for each substation and year
Step 3 - Apply business rules for substations 4,5,6 and 7
There are a few tools in here that are particularly helpful for this use case, and I'd recommend the Tool Mastery sessions below to get more comfortable with them:
Transpose - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Transpose/ta-p/89741
Cross Tab - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Cross-Tab/ta-p/4368
Dynamic Rename - https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Dynamic-Rename/ta-p/32...
Hopefully this gets you close to the output you need. For your final steps of writing to the formatted Excel template, check out the article below: