Need to have hierarchical target data

subhasreemitra

Hi Team,

I have attached a file here which has two tabs - source and target. The source tab has input data and the target tab has the required output data format. I need help there to format the data as mentioned in target tab.

I have tried with Sorting, ID generating and cross tab but not finally have the correct formatted data.

Thanks & Regards,

Subhasree

Hierarchy.xlsx

atcodedog05

Hi @subhasreemitra 

Here is how you can do it. With the help of summarizing and unioning.

Workflow:

1. Using record ID tool to set row id which is later used for sorting.

2. Using summarize to groupby each level and taking first record id.

3. Using union tool to merge them all.

4. Using sort tool to sort by record id and category hierarchy.

5. Using formula tool to turn the not needed higher hierarchy to blank.

Hope this helps : )

hierarchical.yxmd