Best way to align data?

[Hakimipous]

Hello ! 

Lately while using Alteryx at work,  after cleaning a lot of data, the final step I had to do was to align data  

Most of the time I had data following this format

A
	B
		C
			D

And I had to reach this format :

A	B	C	D

Now I know there is a way to deal with this with the Multi-Row formula with an If loop, but it's fastidious while dealing with a lot of fields. and it allows us to do only +1 or -1 operations.

So I came up with one way to deal with this.

- Split every field with a select tool

- Apply a Sample tool with a"Skip first N records"

- Attach a record ID Tool

- Use a Union tool to all the fields based on the Record ID

This method at the end allows me to align all the Data as it should. 

However, this can take some time while dealing with a lot of fields (i.e. 40) 

So I was wondering what methods would you recommend me to use to gain time? I'm not sure this is the more efficient way to deal with this kind of situations

Thanks!

- 

[KaneG]

If the data is as in your example, then I would use the summarize tool with Max() on each field. If the summarize tool is becoming tedious to configure, then you could:

- Transpose all

- Then Summarize: Group By Name, Max Value

[Hakimipous]

Hey ! Thanks for your reply! 

In reality, data don't look that simple. I tried to show a simple picture, but this one should represent better wahat I'm working with

A
E	B
I	F	C
	J	G	D
		K	H
			L

My apologies for the confusion!

[KaneG · Accepted solution]

OK, a Couple more steps and this is providing that you just want to push the data up and don't really care about the rows,,,

Transpose All

Filter: !IsEmpty(Trim([Value]))

Multi-Row: Group By Name, RowID = [Row-1:RowID]+1

Cross-Tab: Group RowID, Column Headers=Name, Values=Value

Attached is an example of how to do that.

[Hakimipous]

That looks great ! thanks for the tips x) 

I'll try this solution at work