Alteryx Designer Desktop Discussions

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

Lining up disjointed columns within same dataset?

CGIQV
8 - Asteroid

I have a report that I'd like to transform so that it can be batch imported to our software.

 

The report looks something like this:

 

Name: Charlie
    
ExerciseLightMediumHard
Run15102
Jump20102
Swim2055
    
Name: Becky
    
ExerciseLightHard 
Run3012 
Jump3012 
Swim2512 

 

I need this:

 

NameExerciseLightMediumHard
CharlieRun15102
CharlieJump20102
CharlieSwim2055
BeckyRun30 12
BeckyJump30 12
BeckySwim25 12

 

Using the multi-row formula I've been able to create the name column:

 

IF Contains([Field1]],"Name:") THEN trim(Replace([Field1],"Name:",""))
ELSE [Row-1:Name] ENDIF

 

This adds a column and layers in the name until a new name appears.  But I can't seem to figure out how to work with the gap in columns.  When one person doesn't have data in a specific column it is omitted, but I need them all to lineup as shown.

 

Suggestions?

16 REPLIES 16
mceleavey
17 - Castor
17 - Castor

Hi @CGIQV ,

 

I've attached the workflow for you.

This was solved using a good old multi-row formula to reference the row above to add the name to every row.

clipboard_image_0.png

 

I hope this helps.

 

M.



Bulien

CGIQV
8 - Asteroid

This got the first half, but I think is missing the second half.  

 

Names are lining up appropriately which I had a different way of getting done.  My issue is the disjointed columns.  Becky only had data under light and hard, not medium.  The resulting workflow places her hard data under medium:

 

clipboard_image_0.png

mceleavey
17 - Castor
17 - Castor

Ah, apologies I didn't notice the differences.

I've built a simple batch macro which will allow you to loop through all names and this should put them in the right format.

 

I hope this solves it!

 

M.



Bulien

mceleavey
17 - Castor
17 - Castor

...and I've attached the macro here as I think I forgot to save the final version...

 

M.



Bulien

CGIQV
8 - Asteroid

I've been tinkering with this for a while to try and apply it to my data set.  I'm not understanding how the macro is being integrated.

 

The data set was an example to provide a conceptual basis of the issue which was having data layered one on top of another where the columns did not align.  But it may be 100 people and 10 different parameters where each person is only utilizing a few.

 

How would the macro need to be modified to account for this?

mceleavey
17 - Castor
17 - Castor

It will work if the data is consistent, but if there are variations not represented in the example data then it won't handle that.

If you could provide a larger dataset more representative of the format you will be using then I can amend accordingly.

 

All the macro does is individually parses each record into the required format, then unions the results.

 

M.



Bulien

CGIQV
8 - Asteroid

On my own I can get the file to look something like this:

 

ChuckExerciseSlowMediumFastExtra Fast
ChuckRun1254
ChuckJump1582
ChuckSwim1582
ChuckBike1582
MaryExerciseSlowFastExtra Fast 
MaryRun184 
MaryBike563 
GaryExerciseFastExtra Fast  
GaryRun115  
GaryBike45  
GarySwim28  
RyanExerciseMediumExtra Fast  
RyanBike920  

 

But ultimately I want I single header column (edit: I mean row), not each person to have their own header independent header which prevents the data from being consistent top to bottom.

 

Thanks in advance for the help.

mceleavey
17 - Castor
17 - Castor

Are you using the latest macro I attached? The one I'm using renames the columns from the headers dynamically, so you shouldn't get these repeated.

 

I've attached it now, embedded within the workflow.



Bulien

CGIQV
8 - Asteroid

What I meant to say was I can ultimately transposed my dataset into the format I showed in the most recent post, so I've bypassed the need for the name to be layered in as a new column.

 

I still am unsure how the macro is parsing the data from the columns appropriately.

Labels
Top Solution Authors