Are Rank functions case-insensitive?

AndreiA
AndreiA Member
in Beast Mode

I'm trying to get a rank for data ordered by modified date and partitioned by id. id is alphanumeric and case-sensitive in the source system. However, when I apply the rank (or row_number) function I get 2 different ids grouped because of the letter case. Collation mode is set to Binary in the ETL Settings.

Tagged:

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @AndreiA I would try using the str_to_hex function on your id field in the formula tile and then build the rank off of that. This will allow the rank to see those two values as different values rather than the same.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • DashboardDude
    DashboardDude Coach

    Hi @AndreiA ,

    So I would make a formula that is just UPPER(id) and do the rank off that. That way you keep your case sensitive one, but also can do your rank on this new capitalized column.

    Let me know if that works.

    John Le

    You're only one dashboard away. 

    More video solutions at: https://www.dashboarddudes.com/pantry

    Circle.png

    John Le

    You're only one dashboard away.

    Click here for more video solutions: https://www.dashboarddudes.com/pantry

  • AndreiA
    AndreiA Member

    Hi,

    It won't work in my case, the rank function does it for me already as it is case-insensitive.

    For example, id1='123iio' and id2='123iiO'. I need to treat them as two individual accounts.

  • MichelleH
    MichelleH Coach

    @AndreiA Is there a pattern of when a character would be uppercase vs lowercase in your id field? If so, you could create a secondary concatenated id to use for ranking. For example, if the last character is uppercase for Product A and lowercase for Product B you could use a formula like this to create your new partition id:

    case 
when `Product` = 'A' then concat(`id`,'A')
when `Product` = 'B' then concat(`id`,'B')
end
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    @AndreiA I would try using the str_to_hex function on your id field in the formula tile and then build the rank off of that. This will allow the rank to see those two values as different values rather than the same.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • AndreiA
    AndreiA Member

    @MarkSnodgrass That's a great idea and it works, thanks a lot!

  • jroach
    jroach Member
    https://community-forums.domo.com/main/discussion/comment/61171#Comment_61171

    This just saved my butt.

Categories