sort in results of Browse tool are not in order

[gshortr]

I am trying to troubleshoot why two records seem to be sorting differently than they should in the results of a Browse tool. When I sort these records, this is how they turn out. This is important as these two records are being displayed differently from other 525- records in later parts of the workflow when they should be treated the same. 

525-001
525-002
525-006
525-007
525-020
525-021
525-022
525-023
525-025
525-026
525-033
525-054
525-055
525-998
525‐059
525‐065

I have cleansed the data and cannot figure out how to identify the characteristics of these two records that are making them appear in the incorrect order (and also I suspect, therefore being treated differently later in the workflow).

Thank you!

Greg

[Morgan_Thomas]

Hi there, 

I'm not sure why it sorts that way, it likely has something to do with the field being a string due to the "-". I think the attached solution could be a good work around. I've separated out the last three digits, converted that field to a number, and then sorted off of that (though if there is ever a case where it is more than 3 digits after the dash, you could do a parse based in regex).

Thanks!

[Morgan_Thomas · Accepted solution]

This was actually still bugging me, so I looked closer. I was going to provide a solution where you could parse based on "-" so it would be dynamic in terms of # of digits after the "-", but i found something really interesting. When I added a text to column tool with a delimiter of "-" it parsed all fields except for those 2 you were having trouble sorting. I think instead of a hyphen there is some kind of different character there. I copy and pasted the numbers from your post, so i'm pretty sure we have the same data. I've attached an updated workflow with the text to column tool configured so you can see for yourself. 

Also in the workflow is a text input tool where I pasted in your values but updated the last 2 fields to replace that character with a hyphen. Once I did that and added a sort tool, they sorted correctly. 

Basically, that character in the last 2 numbers looks exactly like a hyphen but alteryx sees it differently. Same with excel when I tested it in there. I can't explain that but hopefully this helps.

Thanks! 

[Luke_C]

@Morgan_Thomas is exactly right, two different hyphens:

https://www.babelstone.co.uk/Unicode/whatisit.html

[Morgan_Thomas]

Thank you, Luke! 

[gshortr]

This is great Morgan! Thanks so much for solving that. That was my first post on here, so it feels great to see such a fast and helpful response. You made my day 🙂 

I will check out your workflow and see if I can replicate it on my end and mark this off as solved. You rock.

All the best,

Greg

[Morgan_Thomas]

Awesome! I've also attached an example with a new formula tool that will replace the different hyphen types with a regular hyphen, then if you put a sort afterward, it sorts correctly. This may be the easiest approach.