Filter with semantic version

user15995

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.

ST_Superman

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'
end

This 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'

ST_Superman

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'
END

user15995

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
end

Definitely uglier than what you had, but it seems to work.

ST_Superman

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'
end

user15995

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.

ST_Superman

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'
end

This 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'

user15995

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!