Are Rank functions case-insensitive?

AndreiA · 2022-11-28T14:26:44+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Magic ETL

Accepted answers

MarkSnodgrass

@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.

All comments

DashboardDude

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

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

@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

AndreiA

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

jroach

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

This just saved my butt.