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

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
CGIQV
8 - Asteroid

Basically I solved up to here with my own method already:

 

clipboard_image_0.png

But conceptually I still don't understand how the macro is combining the data from the individuals even though they are in different columns.

mceleavey
17 - Castor
17 - Castor

Ok, so you can right click on the macro and select "Open Macro":

 

clipboard_image_0.png

 

This will show you the following:

 

clipboard_image_1.png

 

So the input is the input from the stream, and the control parameter is the field we're going to use as the batch (the field that we'll loop through). This then updates the filter value, which in effect means we'll filter by the first the name on the list, then format the data, then we'll go to the second name on the list, format the data and union to the first name and so on until all names are processed.

 

This is called a batch macro, and you can read more about how they work here:

 

https://help.alteryx.com/2018.2/BatchMacro.htm

 

Hope this helps.

 

M.

 



Bulien

CGIQV
8 - Asteroid

Thanks for the explanation and link.

 

I'll see if I can work through this and report back.

CGIQV
8 - Asteroid

Definitely understanding this better now.

 

I've manipulated some data and still gotten it to work.  I don't know to skip the first two rows so I took that out of the macro.

 

My problem is the size of the data set.  When I change from the current 2 row 5 column structure, I end up getting no results beyond the macro.  Do I need to modify the input on the macro beforehand so that the template shows exactly how many rows there will be?  how would this work if the input file constantly had a different number of names?

 

 

mceleavey
17 - Castor
17 - Castor

Without seeing the data I'm afraid I can't help any further.

If you can provide me with the data I can build it for you and explain how it works.

 

M.



Bulien

CGIQV
8 - Asteroid

Was about to shoot over a different data set, but I ran it through the workflow and it worked without an issue!

 

I must have tinkered with someone accidentally so I re downloaded the package and went from there.  Went without a hitch.  More importantly, it makes sense now so I should be able to reproduce in the future.

 

Thanks again for the help.

mceleavey
17 - Castor
17 - Castor

Fantastic!

 

No problem. Glad I could help.

 

M.



Bulien

Labels