Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Best way to align data?

Hakimipous
10 - Fireball

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 :

 

ABCD
    
    
    

 

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!

 

 

4 REPLIES 4
KaneG
Alteryx Alumni (Retired)

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
10 - Fireball

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   
EB  
IFC 
 JGD
  KH
   L

 

My apologies for the confusion!

KaneG
Alteryx Alumni (Retired)

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
10 - Fireball

That looks great ! thanks for the tips x) 

 

I'll try this solution at work 

Labels