Are Rank functions case-insensitive?

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 Portland, Oregon 🥷
    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

  • 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

  • AndreiA
    AndreiA London, UK ⚪️

    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.

  • @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
    
  • AndreiA
    AndreiA London, UK ⚪️

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