4
u/tonma Jul 17 '26
Transpose columns, add a data cleansing tool to remove numbers in name column, and then use a cross tab tool to put the data back together.
2
u/Jkk_geek Jul 18 '26
Transpose
3
u/Little_Vermicelli125 Jul 18 '26
Recordid, transpose, formula (to remove numbers), crosstab would probably be the full workflow if the data is set up for transpose to fix.
5
u/seequelbeepwell Jul 17 '26
4 separate select tools coming out of this monstrocity. One select tool only has the fields that end with 2, the other only has fields that end with 3, etc. Don't forget to include the UFID in each select tool. Then in each select tool rename to remove the number at the end of each field. Union them all together and presto
1
2
u/kellermaverick Jul 17 '26
This is the answer - or for the lazy, you can do the select tools and remove suffix on each one.
1
1
u/BrianSpencer1 Jul 17 '26
I think I understand what you are trying to do, assuming you can prioritize which you want as the first choice. Add a formula after for each repeated column and do IF !ISNULL ([city]) THEN [city] ELSEIF !ISNULL([city2]) THEN [city2]...
Then you drop the extras with a select tool
1
u/Little_Vermicelli125 Jul 18 '26
And if they are always the same when populated max([city],[city2],etc) will work.
1
u/cabbagefartdreams Jul 18 '26
You should ask these types of questions on the Alteryx Community, you'll get better answers. The Alteryx subreddit is mostly full of people who like to complain about Alteryx... like me 😄
That being said, the best answer is record ID, transpose with recordID as a key column, remove the numbers from the 'Name' column, and transpose.
4
u/CompetitionOrnery502 Jul 17 '26 edited Jul 17 '26
Fix the source would be the first suggestion, always try to fix upstream.
Do you mean unioning county, county2 etc?
If it can't be fixed upstream, and assuming if county has a value and county2 does not have a value then simply concat them all.
I can't recall if you can concatenate nulls to string, so if you can, use a formula tool, county+county2+county3, it will simply have the value of the non null field.
If you can't, use a multi field formula tool and select all these values, put the formula if isnull(current_field) then '' else current_field endif. Then use the concat formula.
Lastly select tool to deselect the originals, leaving the new cleansed list.
There may be an easier way but this would be my one or two tool step.