Multi-Row and Multi-Field tools with hierarchies

[jaydelim16]

Hello,

Please find attached a workbook with the below problem regarding hierarchies:

-  "Input" tab -->  For Alteryx Input

-  I used the Multi-Field formula tool, followed by the Multi-Row formula tool, in order to come up with the "Intermediary" tab

-  I need a third step to come up with the "Output" tab, which is the final output that I want.  

I have 3 questions:

1.  Is there a shortcut to use only 1 or 2 tools instead of 5 multi-field formula one after the other?

2.  Is there a shortcut to use only 1 or 2 tools instead of 6 multi-row formula one after the other?

3.  Is there a way to output everything but the bolded rows [like in the "Output" tab]?  I don't need the various bolded headers from the "Input" tab to copy all the way to hierarchy 6 in the output tab

I started a workflow.  Please help me fix it so I can get from the "Intermediary" tab to the "Output" tab.

Thank you!

[atcodedog05]

Hi @jaydelim16 

You dont need multiple multi-field formula tool you can use the existing formula to write the formula (refer to highlighted below). You can escape from using multiple multi-row formula tool by using (transpose) - ( multi-row formula) - (crosstab) this can handle n number of columns with same set of tools.

Workflow:

Hope this helps : )

[atcodedog05]

Hi @jaydelim16 

Updated the workflow with removing bolded rows. This part is bit tricky and will work only for till Hierarchy 6.

Workflow:

Hope this helps : )

[Pang_Hee_Choy · Accepted solution]

add dynamic rename to solve if have higher the hierarchy.

use multiple field to remove line.

use multiple row to copy to right. and down

and identify last hierarchy by identify whether the next row and column have data. i.e. In A1, if B2 have data, it mean A1 is not the last hierarchy. 

here is the answer,

1.  Is there a shortcut to use only 1 or 2 tools instead of 5 multi-field formula one after the other?

     RecordID > transpose > multiple row (group by RecordID) > crosstab.

2.  Is there a shortcut to use only 1 or 2 tools instead of 6 multi-row formula one after the other?

     RecordID > transpose > multiple row (group by Name) > crosstab.

    when work with multiple/dynamic columns, mostly are using these combination.

3.  Is there a way to output everything but the bolded rows [like in the "Output" tab]?  I don't need the various bolded headers from the "Input" tab to copy all the way to hierarchy 6 in the output tab

     We have to identify the pattern, find a logic/ unique fact that able to differentiate them.

    I tried manual filter at Hierarchy 6, user to input all the department or place name then filter via join.

    but it too manual hence, i continue find if there have any work around. And I found the better one.

     and one important thing, try to ask whether the source have other pattern. sometime their already have a proper format/ format that require less job.

[jaydelim16]

Thank you for the shortcut - definitely helpful for this and other workflows!

[jaydelim16]

I was hoping for Hierarchy 1 to still be 'Organization', Hierarchy 2 to be 'Distribution' or 'Pricing' etc. rather than only the names, but there's a lot of combination of tools that I'm learning from your workflow so I appreciate it!!

[jaydelim16]

Thank you @Pang_Hee_Choy ! Exactly what I was looking for.