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.
SOLVED

Creating A Report From A Tree Hierarchy

mgcarrenard
7 - Meteor

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_datebusiness_unitdepartmentproduct transaction_amount 
2179-01-01100120013001 $           31,000,000.00
2179-01-01100220023001 $           31,000,000.00
2179-01-01100320033001 $           16,000,000.00
2179-01-01100420043003 $           28,000,000.00
2179-01-02100420053004 $           27,000,000.00
2179-01-02100420063005 $           20,000,000.00
2179-01-02100520073006 $           32,000,000.00
2179-01-02100520083007 $           26,000,000.00
2179-01-03100520093008 $           25,000,000.00
2179-01-03100520103009 $           25,000,000.00
2179-01-03100520013010 $           20,000,000.00
2179-01-03100520023001 $           23,000,000.00
2179-01-03100620033001 $           25,000,000.00
2179-01-04100620043002 $           28,000,000.00
2179-01-05100620053002 $           16,000,000.00
2179-01-05100620063001 $           17,000,000.00
2179-01-05100620073001 $           23,000,000.00
2179-01-05100620083002 $           22,000,000.00
2179-01-05100620093001 $           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

 

dimensiondimension_labelnode_nameparentisLeafvalue
1Business UnitAll Business UnitsNullFALSE1.
1Business UnitWeyland-Yutani1.FALSE1.1.
1Business UnitWeyland-Yutani Heavy Industries1.1.TRUE1001
1Business UnitWeyland-Yutani Mining Company1.1.TRUE1002
1Business UnitLexCorp1.FALSE1.2.
1Business UnitLexCorp Manufacturing1.2.TRUE1003
1Business UnitLexCorp Biotech Limited1.2.TRUE1004
1Business UnitTyrell Corporation1.FALSE1.3.
1Business UnitTyrell Replicant Division1.3.TRUE1005
1Business UnitTyrell Neon Sign Company1.3.TRUE1006
2DepartmentAll DepartmentsNullFALSE2.
2DepartmentManufacturing2.FALSE2.1.
2DepartmentAutomated Nanofabrication2.1.TRUE2001
2DepartmentExtraterrestial Procurement2.1.TRUE2002
2DepartmentShipping2.FALSE2.2.
2DepartmentTeleportation Courrier Services2.2.TRUE2003
2DepartmentSpace Shipping2.2.TRUE2004
2DepartmentCorporate2.FALSE2.3.
2DepartmentManagement2.3.TRUE2005
2DepartmentOffice Services2.3.TRUE2006
2DepartmentEmployee Services2.3.FALSE2.3.1.
2DepartmentA.I. Employees2.3.1.TRUE2007
2DepartmentMeatbag Employees2.3.1.TRUE2008
2DepartmentResearch & Development2.FALSE2.4.
2DepartmentVR Simulation Analytics2.4.TRUE2009
2DepartmentDimensional Scrying Team2.4.TRUE2010
3ProductAll ProductsNullFALSE3.
3ProductSpacecraft3.FALSE3.1.
3ProductWarships3.1.FALSE3.1.1.
3ProductBattlestar Class Ships3.1.1.FALSE3.1.1.1.
3ProductGalactica Model3.1.1.1.TRUE3001
3ProductPegasus Model3.1.1.1.TRUE3002
3ProductViper Class Ships3.1.1.TRUE3003
3ProductHeavy Freighters3.1.TRUE3004
3ProductGeneration Ships3.1.TRUE3005
3ProductWeapons and Defense Systems3.FALSE3.2.
3ProductPlasma Blasters3.2.TRUE3006
3ProductForcefield Generators3.2.TRUE3007
3ProductSpace Minerals3.FALSE3.3.
3ProductKryptonite3.3.TRUE3008
3ProductUnobtainium3.3.TRUE3009
3ProductClones3.FALSE3.4.
3ProductStandard Clones3.4.TRUE3010
3ProductEnhanced Clones3.4.TRUE3011
3ProductRobots3.FALSE3.5.
3ProductAttack Drones3.5.TRUE3012
3ProductAndroids3.5.TRUE3013
3ProductMisc3.FALSE3.7.
3ProductCryogenic Preservation Services3.7.TRUE3014
3ProductNeon Signs3.7.TRUE3015

 

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_idline_item_idline_item_labelCategorybusiness_unitdepartmentproductsign
RPT1item_1SalesRevenues1.1. ; 1.3.2.1; 2.2V30011
RPT1item_2CostsExpenses1.1. ; 1.3.2.3 ; 2.4V3001-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_labelCategory Amount 
SalesRevenues $  110,000,000.00
CostsExpenses $  (59,000,000.00)

 

 

How would you guys recommend I tackle this sort of task? 

 

Thank you!

5 REPLIES 5
LordNeilLord
15 - Aurora

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..

 

Transactions.PNG

LordNeilLord
15 - Aurora

@mgcarrenard

 

This is more like it:

 

Transactions2.PNG

mgcarrenard
7 - Meteor

That's awesome! You just condensed several hundred lines of VBA code to a single Alteryx Worksheet! Very elegant solution.

 

Cheers

LordNeilLord
15 - Aurora

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.

 

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome


Data Lover

LordNeilLord
15 - Aurora

@ChristineB, something like this would be a really good weekly challenge :)

 

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome


Data Lover

Labels