Filter with semantic version
Hi. I'm looking for a way in Domo to filter (and sort) values in a card using a semantic version. For example, 5.7.12 is "higher" than 5.7.8. In a card, I'd like to only show versions that are lower than 5.7.12. Since this value is a string, the options for filtering are to manually check the box for each version individually. Note that sorting does appear to work.
Best Answer
-
You can try this:
case
when `mysql_major_version` > 5 then 'false'
when `mysql_major_version` < 5 then 'true'
-- if the major version is equal to 5 the case statement will move on to the next when statement
when `mysql_minor_version` > 7 then 'false'
when `mysql_minor_version` < 7 then 'true'
-- if the Minor version is equal to 7 the case statement will move on to the next when statement
when `mysql_patch_version` > 7 then 'false'
when `mysql_patch_version` < 8 then 'true'
else 'did not match a when clause'
endThis way you should be able to identify which values are falling all the way through the case statement. Are there any null values for major version, minor version, or patch version? that would cause the original case statement to return a true value, in this new statement it would return a 'did not match a when clause'
1
Answers
-
I think that you could write a beastmode that would let you filter your data:
CASE
WHEN `Verison` < '5.7.12' THEN 'Less than 5.7.12'
ELSE '5.7.12 or Later'
END0 -
Thanks, I ended up breaking down the fields using ETL into major, minor, and patch versions. I should note too that most versions had a "-log" or something on the end, so they weren't super pretty. Here is what I ended up with:
case
when `mysql_major_version` > 5 then 'false'
when `mysql_major_version` < 5 then 'true'
when `mysql_major_version` = 5
then
case
when `mysql_minor_version` > 7 then 'false'
when `mysql_minor_version` < 7 then 'true'
when `mysql_minor_version` = 7
then
case
when `mysql_patch_version` > 7 then 'false'
when `mysql_patch_version` < 8 then 'true'
end
end
endDefinitely uglier than what you had, but it seems to work.
0 -
I don't like seeing all of those nested case statements. Could you try something like this? Just to clean it up a little bit?
case
when `mysql_major_version` > 5 then 'false'
when `mysql_major_version` < 5 then 'true'
-- if the major version is equal to 5 the case statement will move on to the next when statement
when `mysql_minor_version` > 7 then 'false'
when `mysql_minor_version` < 7 then 'true'
-- if the Minor version is equal to 7 the case statement will move on to the next when statement
when `mysql_patch_version` > 7 then 'false'
else 'true'
end1 -
FYI - I attempted the first solution, and the results for a "true" condition were significantly higher than I would expect. Have you tried something similar with success? I also tried the second solution, and the results were also different from the calculation that I posted above. Not by much - there were two more values in the "true" set. I'll see if I can figure out which formula is returning the correct values.
Also a quick clarification: In my original post I referenced 5.7.12, but I later used the value 5.7.8 as the value to compare against. Apologies for the confusion.
0 -
You can try this:
case
when `mysql_major_version` > 5 then 'false'
when `mysql_major_version` < 5 then 'true'
-- if the major version is equal to 5 the case statement will move on to the next when statement
when `mysql_minor_version` > 7 then 'false'
when `mysql_minor_version` < 7 then 'true'
-- if the Minor version is equal to 7 the case statement will move on to the next when statement
when `mysql_patch_version` > 7 then 'false'
when `mysql_patch_version` < 8 then 'true'
else 'did not match a when clause'
endThis way you should be able to identify which values are falling all the way through the case statement. Are there any null values for major version, minor version, or patch version? that would cause the original case statement to return a true value, in this new statement it would return a 'did not match a when clause'
1 -
Nailed it. There were two blank values in the set that were being filtered out by my formula but not by your initial formula. Your modification allowed me to easily identify them.
Thanks!
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive