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:
Recid | Employee | Manager Hierarchy |
1 | sara smith | jones,jeff|den,david|hour,sam|glen,judy |
2 | john jones | hour,sam|glen,judy |
3 | becky wyse | wane,hi|jones,jeff|den,david|hour,sam|glen,judy |
4 | harold good | jasper,jen|jones,jeff|den,david|hour,sam|glen,judy |
5 | hi wane | jones,jeff|den,david|hour,sam|glen,judy |
6 | david den | hour,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!!!!!
Recid | Employee | Manager Hierarchy | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
1 | sara smith | jones,jeff|den,david|hour,sam|glen,judy | glen,judy | hour,sam | den,david | jones,jeff | jones,jeff | jones,jeff | jones,jeff | jones,jeff | jones,jeff |
2 | john jones | hour,sam|glen,judy | glen,judy | hour,sam | hour,sam | hour,sam | hour,sam | hour,sam | hour,sam | hour,sam | hour,sam |
3 | becky wyse | wane,hi|jones,jeff|den,david|hour,sam|glen,judy | glen,judy | hour,sam | den,david | jones,jeff | wane,hi | wane,hi | wane,hi | wane,hi | wane,hi |
4 | harold good | jasper,jen|jones,jeff|den,david|hour,sam|glen,judy | glen,judy | hour,sam | den,david | jones,jeff | jasper,jen | jasper,jen | jasper,jen | jasper,jen | jasper,jen |
5 | hi wane | jones,jeff|den,david|hour,sam|glen,judy | glen,judy | hour,sam | den,david | jones,jeff | jones,jeff | jones,jeff | jones,jeff | jones,jeff | jones,jeff |
6 | david den | hour,sam|glen,judy | glen,judy | hour,sam | hour,sam | hour,sam | hour,sam | hour,sam | hour,sam | hour,sam | hour,sam |
Solved! Go to Solution.
Hi @amw1 ,
Attached is an example showing how to get that done.
Let me know if that works for you.
Best,
Fernando Vizcaino
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.
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!
This works great - thank you again!!!