Apologies in advance for the long-winded description but I'm not sure of a better way of explaining it.
I have an Excel input which contains a hierarchical structure of seven levels, together with the lowest level units, all within the same column. The delimination between the levels is by way of indentation which is lost upon input into Alteryx. I therefore have an Excel macro which counts the number of indents and returns this as a value in a new field, which becomes our Level n. e.g.
| Item/hierarchy | Level (count of indents) |
| A | 0 |
| B | 1 |
| C | 2 |
| D | 3 |
| E | 4 |
| F | 5 |
| Item x | 6 |
| Item y | 6 |
| G | 5 |
| Item z | 6 |
In the above example, Item x and Item y are the lowest level and have all hierarchical attributes of those above them. Item z shares the hierarchical attributes of levels 1-4 but its level 5 is G. The expected result is:
| Level 0 | Level 1 | Level 2 | Level 3 | Level 4 | Level 5 | Level 6 |
| A | B | C | D | E | F | Item x |
| A | B | C | D | E | F | Item y |
| A | B | C | D | E | G | Item z |
I think I have an adequate solution by way of multiple, multi-row formulae (one for each new field). My questions are:
- Is there a better way of achieving the same result?
- can I turn this into a batch macro (?) using the number of indents as a control parameter so that I can use this in my workflow rather than having numerous multi-row formulae?
Thanks
