Filter with semantic version

Reply
Highlighted
Visitor

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.


Accepted Solutions
Black Belt

Re: Filter with semantic version

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
______________________________________________________________________________________________

All Replies
Black Belt

Re: Filter with semantic version

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
______________________________________________________________________________________________
Visitor

Re: Filter with semantic version

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.

Black Belt

Re: Filter with semantic version

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
______________________________________________________________________________________________
Visitor

Re: Filter with semantic version

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.

Black Belt

Re: Filter with semantic version

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
______________________________________________________________________________________________
Visitor

Re: Filter with semantic version

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!

Announcements
Looking for the latest Community solutions? Please visit our accepted solutions board here!