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

  • ST_-Superman-_
    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'
    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'


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

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

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • 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.

  • 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

    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • 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-_
    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'
    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'


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • 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!

This discussion has been closed.