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

Backwards fill dynamic columns based on parsing field

amw1
7 - Meteor

Hi - I have to split out a field of data into a dynamic number of columns and then backfill any empty ones so that all rows have the same number of columns, I currently hard-coded a set number by adding to the field I want to parse but I am wondering if there is a way to do this dynamically.

 

The field that I am parsing has data similar to: jones,jeff|den,david|hour,sam|glen,judy  where the pipe character is used to separate out each person. The values can remain as last name, firstname. 

 

so this data:

RecidEmployeeManager Hierarchy
1sara smithjones,jeff|den,david|hour,sam|glen,judy
2john joneshour,sam|glen,judy
3becky wysewane,hi|jones,jeff|den,david|hour,sam|glen,judy
4harold goodjasper,jen|jones,jeff|den,david|hour,sam|glen,judy
5hi wanejones,jeff|den,david|hour,sam|glen,judy
6david denhour,sam|glen,judy

 

Becomes data shown below.

 

Notice that the first name found in the field being parsed is the one used to fill remaining columns. I attached my workflow. I initially tried to count the number of pipes and then wanted to use that to know how many rows to add for each record to bring the number of rows to the max and then pivot them but I couldn't figure out how to find the maximum number of columns needed from the entire data set. 

 

Thank you for all the help in this!!!!!

 

 

RecidEmployeeManager Hierarchy123456789
1sara smithjones,jeff|den,david|hour,sam|glen,judyglen,judyhour,samden,davidjones,jeffjones,jeffjones,jeffjones,jeffjones,jeffjones,jeff
2john joneshour,sam|glen,judyglen,judyhour,samhour,samhour,samhour,samhour,samhour,samhour,samhour,sam
3becky wysewane,hi|jones,jeff|den,david|hour,sam|glen,judyglen,judyhour,samden,davidjones,jeffwane,hiwane,hiwane,hiwane,hiwane,hi
4harold goodjasper,jen|jones,jeff|den,david|hour,sam|glen,judyglen,judyhour,samden,davidjones,jeffjasper,jenjasper,jenjasper,jenjasper,jenjasper,jen
5hi wanejones,jeff|den,david|hour,sam|glen,judyglen,judyhour,samden,davidjones,jeffjones,jeffjones,jeffjones,jeffjones,jeffjones,jeff
6david denhour,sam|glen,judyglen,judyhour,samhour,samhour,samhour,samhour,samhour,samhour,samhour,sam
4 REPLIES 4
fmvizcaino
17 - Castor
17 - Castor

Hi @amw1 ,

 

Attached is an example showing how to get that done.

fmvizcaino_0-1580334493638.png

 

Let me know if that works for you.

Best,

Fernando Vizcaino

NickSm
Alteryx
Alteryx

Hey @amw1 

 

You can make it a little more dynamic than hard-coding in a number of pipes by just doing an extra pivot before you do your fill-down.  Or you can make use of the tile tool as shown above.

 

testhierarchy.PNG

amw1
7 - Meteor

Thank you I see how this works but I would like to not have the hard-coded 9 in there. I see another solution was posted so I'll take a look at that as well but what you posted was definitely better than my original. Thank you again!

amw1
7 - Meteor

This works great - thank you again!!!

Labels