Hi all,
I have a Excel VBA business process that I am trying to replicate in Alteryx and wanted to ask about the best way to proceed.
I want to start from the following Input:
1)- A list of transactions
2)- A Hierarchical Tree containing information about the different columns in said transaction list
3)- A report template/layout file
And produce the following Output:
4)- Summarized Transaction Report
1) My list of transactions would look like this
transaction_date | business_unit | department | product | transaction_amount |
2179-01-01 | 1001 | 2001 | 3001 | $ 31,000,000.00 |
2179-01-01 | 1002 | 2002 | 3001 | $ 31,000,000.00 |
2179-01-01 | 1003 | 2003 | 3001 | $ 16,000,000.00 |
2179-01-01 | 1004 | 2004 | 3003 | $ 28,000,000.00 |
2179-01-02 | 1004 | 2005 | 3004 | $ 27,000,000.00 |
2179-01-02 | 1004 | 2006 | 3005 | $ 20,000,000.00 |
2179-01-02 | 1005 | 2007 | 3006 | $ 32,000,000.00 |
2179-01-02 | 1005 | 2008 | 3007 | $ 26,000,000.00 |
2179-01-03 | 1005 | 2009 | 3008 | $ 25,000,000.00 |
2179-01-03 | 1005 | 2010 | 3009 | $ 25,000,000.00 |
2179-01-03 | 1005 | 2001 | 3010 | $ 20,000,000.00 |
2179-01-03 | 1005 | 2002 | 3001 | $ 23,000,000.00 |
2179-01-03 | 1006 | 2003 | 3001 | $ 25,000,000.00 |
2179-01-04 | 1006 | 2004 | 3002 | $ 28,000,000.00 |
2179-01-05 | 1006 | 2005 | 3002 | $ 16,000,000.00 |
2179-01-05 | 1006 | 2006 | 3001 | $ 17,000,000.00 |
2179-01-05 | 1006 | 2007 | 3001 | $ 23,000,000.00 |
2179-01-05 | 1006 | 2008 | 3002 | $ 22,000,000.00 |
2179-01-05 | 1006 | 2009 | 3001 | $ 19,000,000.00 |
The <transaction_date> and <transation_amount> fields are pretty self explanatory. The <business_unit>, <department>, <product> are recorded using an internal naming scheme that is determined by the following tree structure:
2) - Hierarchy/Tree Structure
dimension | dimension_label | node_name | parent | isLeaf | value |
1 | Business Unit | All Business Units | Null | FALSE | 1. |
1 | Business Unit | Weyland-Yutani | 1. | FALSE | 1.1. |
1 | Business Unit | Weyland-Yutani Heavy Industries | 1.1. | TRUE | 1001 |
1 | Business Unit | Weyland-Yutani Mining Company | 1.1. | TRUE | 1002 |
1 | Business Unit | LexCorp | 1. | FALSE | 1.2. |
1 | Business Unit | LexCorp Manufacturing | 1.2. | TRUE | 1003 |
1 | Business Unit | LexCorp Biotech Limited | 1.2. | TRUE | 1004 |
1 | Business Unit | Tyrell Corporation | 1. | FALSE | 1.3. |
1 | Business Unit | Tyrell Replicant Division | 1.3. | TRUE | 1005 |
1 | Business Unit | Tyrell Neon Sign Company | 1.3. | TRUE | 1006 |
2 | Department | All Departments | Null | FALSE | 2. |
2 | Department | Manufacturing | 2. | FALSE | 2.1. |
2 | Department | Automated Nanofabrication | 2.1. | TRUE | 2001 |
2 | Department | Extraterrestial Procurement | 2.1. | TRUE | 2002 |
2 | Department | Shipping | 2. | FALSE | 2.2. |
2 | Department | Teleportation Courrier Services | 2.2. | TRUE | 2003 |
2 | Department | Space Shipping | 2.2. | TRUE | 2004 |
2 | Department | Corporate | 2. | FALSE | 2.3. |
2 | Department | Management | 2.3. | TRUE | 2005 |
2 | Department | Office Services | 2.3. | TRUE | 2006 |
2 | Department | Employee Services | 2.3. | FALSE | 2.3.1. |
2 | Department | A.I. Employees | 2.3.1. | TRUE | 2007 |
2 | Department | Meatbag Employees | 2.3.1. | TRUE | 2008 |
2 | Department | Research & Development | 2. | FALSE | 2.4. |
2 | Department | VR Simulation Analytics | 2.4. | TRUE | 2009 |
2 | Department | Dimensional Scrying Team | 2.4. | TRUE | 2010 |
3 | Product | All Products | Null | FALSE | 3. |
3 | Product | Spacecraft | 3. | FALSE | 3.1. |
3 | Product | Warships | 3.1. | FALSE | 3.1.1. |
3 | Product | Battlestar Class Ships | 3.1.1. | FALSE | 3.1.1.1. |
3 | Product | Galactica Model | 3.1.1.1. | TRUE | 3001 |
3 | Product | Pegasus Model | 3.1.1.1. | TRUE | 3002 |
3 | Product | Viper Class Ships | 3.1.1. | TRUE | 3003 |
3 | Product | Heavy Freighters | 3.1. | TRUE | 3004 |
3 | Product | Generation Ships | 3.1. | TRUE | 3005 |
3 | Product | Weapons and Defense Systems | 3. | FALSE | 3.2. |
3 | Product | Plasma Blasters | 3.2. | TRUE | 3006 |
3 | Product | Forcefield Generators | 3.2. | TRUE | 3007 |
3 | Product | Space Minerals | 3. | FALSE | 3.3. |
3 | Product | Kryptonite | 3.3. | TRUE | 3008 |
3 | Product | Unobtainium | 3.3. | TRUE | 3009 |
3 | Product | Clones | 3. | FALSE | 3.4. |
3 | Product | Standard Clones | 3.4. | TRUE | 3010 |
3 | Product | Enhanced Clones | 3.4. | TRUE | 3011 |
3 | Product | Robots | 3. | FALSE | 3.5. |
3 | Product | Attack Drones | 3.5. | TRUE | 3012 |
3 | Product | Androids | 3.5. | TRUE | 3013 |
3 | Product | Misc | 3. | FALSE | 3.7. |
3 | Product | Cryogenic Preservation Services | 3.7. | TRUE | 3014 |
3 | Product | Neon Signs | 3.7. | TRUE | 3015 |
The tree follows a Node/Leaf structure with the values in the transaction list corresponding to the node value in the tree. For example, Node 3.1.1.1 (Product > Spacecraft > Warships > Battlestar Class Ships) contains the leaves 3001 - Galactica Model and 3002 - Pegasus Model.
This tree is a bit easier to see in the attached Excel file as I colour-coded it for easier reading.
3)- The report template/layout looks like this:
report_id | line_item_id | line_item_label | Category | business_unit | department | product | sign |
RPT1 | item_1 | Sales | Revenues | 1.1. ; 1.3. | 2.1; 2.2 | V3001 | 1 |
RPT1 | item_2 | Costs | Expenses | 1.1. ; 1.3. | 2.3 ; 2.4 | V3001 | -1 |
The transaction dimensions can be expressed either as a Node (collection of leaves) or by the value of the leaf preceded by a 'V' (So 3001 - Galactica Model would be represented by 'V3001'). For an extra challenge, there is also a 'sign' column to indicate the sign of the final summarized value.
4) - If I run the report based on the layout above. To calculate "Sales", it would select the transactions in the transactions list where the business_unit is contained in nodes (1.1 OR 1.3), the department is contained in nodes (2.1 OR 2.2) and the product is 3001. It would then multiply the final result by 1. The result would look like this:
line_item_label | Category | Amount |
Sales | Revenues | $ 110,000,000.00 |
Costs | Expenses | $ (59,000,000.00) |
How would you guys recommend I tackle this sort of task?
Thank you!
Solved! Go to Solution.
Hey @mgcarrenard,
I'm almost there with this!
I just need to figure out how the get the value if it is nested (for example employee services)
He's what I've got so far if you want to have a play..
That's awesome! You just condensed several hundred lines of VBA code to a single Alteryx Worksheet! Very elegant solution.
Cheers
no worries @mgcarrenard
This was bugging me all weekend but I finally got there.
I'm not sure how robust this is going to be on your actual data...but let me know if it works out alright.
Part time Tableau, Part Time Alteryx. Full Time Awesome
@ChristineB, something like this would be a really good weekly challenge :)
Part time Tableau, Part Time Alteryx. Full Time Awesome