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.
Best 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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.3
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
John Le
You're only one dashboard away.
Click here for more video solutions:
1 -
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.
0 -
@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
1 -
@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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.3 -
@MarkSnodgrass That's a great idea and it works, thanks a lot!
0 -
This just saved my butt.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive