Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

How to create a complicated spreadsheet by using exisitng data

esun1028
5 - Atom

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.

 

 

 

1 REPLY 1
jamielaird
14 - Magnetar

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

jamielaird_0-1614783353971.png

Step 2 - Calculate MW and % of Total MW for each substation and year

jamielaird_1-1614783392480.png

Step 3 - Apply business rules for substations 4,5,6 and 7

jamielaird_2-1614783421553.png

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:

 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-do-I-output-to-an-Excel-templat...

 

Labels