Multi-Row and Multi-Field tools with hierarchies

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!

Hierarchies.xlsx

Hierarchies.yxmd

Preparation

Accepted answers

AncientPandaman

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.

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.

Hierarchies.yxmd

All comments

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 : )

Hierarchies.yxmd

atcodedog05

Hi @jaydelim16

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