Adjusting and copying data in rows in Alteryx

[Maurio]

Hi,

I am not sure if this is possible in Alteryx, but I have a set of data that I am trying to get into a specific format and I can't figure out how to do it.

The data is a list of hierarchies from a system that are in a list of rows. I have already cleaned up the data so that the parent in the hierarchy appears in the left-most column and the children appear on the right. Each child is one row below and to the right of the parent. This continues for 6 further levels. Here is an example of what it looks like:

What I would like to do, is turn this into a lookup table so that the relevant parent is repeated on each row until you get to the next parent in the file, and the children move up a row to sit alongside them. Here is an example that I have done manually of what I would like to achieve:

The idea is that you could search for any of the children in the table and then just look to the left to see all of the parents it sits under in the hierarchy, rather than navigating up and left to find it right at the top.

I am not sure if this is possible in Alteryx as even doing it manually is quite tricky, but I thought I would ask.

Please let me know if you need any further information.

Thank you.

[Sebastiaandb]

Hi @Maurio ,

This is probably what you're looking for :-):

Greetings, 

Seb

[Maurio]

Thank you Seb, I think this is close to what I need.

Is there a way to have Alteryx move the data in the right-most columns up a row so that it is in line with the parent on the left? If not, your method might still work I think I would just need to remove any duplicate rows.

[Sebastiaandb]

@Maurio Hi!

You mean that you want these removed?

Basically, if there's a child2 available. Only take rows were child 2 is filled. If not, then keep the row with child 2 empty right?

[Sebastiaandb]

Hi @Maurio ,

Try this one :-)

Greetings,

Seb

[Maurio]

Hi Seb,

That's correct, yes. What I am finding with the current method is that in columns further along, data is getting repeated like this:

"PINFLYM" in column DESCE 3 is being repeated against both "PINFL" and PINWA" in column DESCE3. It should only appear against "PINFL". It is appearing against "PINWA" because in the original data that cell is blank, but what I actually want to happen is for the data in the row below to move up one and fill the gap, so next to that row of "PINWA" it should have "PINWAYM". Hope that makes sense.

[Sebastiaandb]

Hi @Maurio ,

I have seen that issue arise indeed in my first version. I changed it in the second version i posted :-). You can do a group by in the multi row tool that is filling in CHILD2 and do the groupby on CHILD1 thereby, it will only take CHILD2's that are related to that specific CHILD1. 

[Maurio]

Hi Seb,

Thank you again. I'm afraid this isn't quite getting me what I am looking for. The group by feature is removing a lot of the fields from the data and I am finding that the multirow formula is meaning that I am ending up with items in the rightmost column against the wrong thing in the left column.

It's a bit tricky because I need it to almost go in reverse, so that the item in the rightmost column moves up to be on the same line as the next item of data in the leftmost column and up. I've mocked up a simplified version of what I am trying to do here:

A
	B
		C
		C

Should become:

A	B	C
A	B	C

The added problem I have is that the data is quite inconsistent and there are gaps with blank columns in certain rows, so the raw data might look more like this:

A
	B
		C
		C
				D
						E

This is why I wasn't sure if Alteryx can do what I need it to. Let me know if you have any other thoughts but thank you in any case for your help.