I would like to reorganize the attached data (image) into two fields; City and Postcode - what would be the best way to do this?
Solved! Go to Solution.
Hi @Gilld,
I'd go for something like this logic, if there are no numbers in the field (and I'm not aware of any UK city with numbers in the name) then it's a city else its a postcode. Then applying this logic to a multirow formula you can get something like this:
Example attached
Part time Tableau, Part Time Alteryx. Full Time Awesome
Thanks for this @LordNeilLord !! Sorry I can't seem to split the field into 'contains numbers' and 'doesn't contain numbers'?
Thanks for this!! Sorry I can't seem to split the field into 'contains numbers' and 'doesn't contain numbers'?
If you use the RegEx_CountMatches([Field1], "\d") = 0 (like I showed in my example) ...which is basically saying, count the numbers in the field
Part time Tableau, Part Time Alteryx. Full Time Awesome
Hi @LordNeilLord, I am getting the attached - not quite sure how this helps?
Hey @Gilld
Your Multirow formula isn't quite right...try this:
If RegEx_CountMatches([City],"\d")=0 Then [City] Else [Row-1:NewField] Endif
And you need to set your newfield to be a string type.
You can use the multirow formula i provided in the original attachement
Part time Tableau, Part Time Alteryx. Full Time Awesome
Thanks @LordNeilLord!
The field has split (attached) - but the postcodes and cities are now muddled up?
That is super weird!
Can you send me the flat file you are using and I can have a proper look?
Part time Tableau, Part Time Alteryx. Full Time Awesome