Compare to text lists and highlight difference

Simon_King
Simon_King Member
in Magic ETL

I have 2 lists and am trying to highlight differences i.e.

 

Value owned                Managed values

value 1                         Value 1

Value 2

Value 3                         Value 3

 

I want to highlight Value 2

 

So far I have used ETL to generate a new dataset with just these 2 columns.   I am then trying to use beastmode using something like NULLIF or a case when 'managed values' ='' then count 'value owned', but this is not working I just get a count of zero (when I know there is at least one difference).  Any ideas?  Code for this is ...

 

SUM(
(CASE
WHEN `ManagedValues` = '' THEN `Valueowned`
ELSE 0
END)
)

Best Answer

  • Simon_King
    Simon_King Member
    Answer ✓

    What I have done is a change of logic - I am just discounting those which have a managed value (marked as Zero) and therefore this leaves those which do not have a corresponding value

     

    (CASE
    WHEN LENGTH(`ManagedDomains` )> 1
    THEN 0
    ELSE 1

    END)

     

    Once you get the logic its so simple ?

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    I see a couple options for your depending on what you are wanting to do. I assume you are using a table card to display your results.

    Option 1 - If you are wanting to literally highlight the field, use color rules

    You could create a color rule for the Managed Values column and set the condition where "in" is blank and then choose whatever fill color you want to highlight that cell.

    Option 2 - Compare Value Owned column to Managed Values column with Beast Mode and make a new column based on the results

    You could create a new beast mode field Different, for example, and the beast mode formula woud look like this:

    (CASE
    WHEN UPPER(`ManagedValues`) <> UPPER(`Valueowned`)

    THEN 'Different'
    ELSE ''
    END)

    You could then create a color rule that would highlight where the beast mode column equals "different" or use a quick filter to only show those rows with the value of "different".

    Option 3 - Same as option 2 but allows for count 

    If you replace the THEN ... ELSE... synatax above with THEN 1 ELSE 0, you could then easily add the total row in the table properties to sum that up and get your total number of differences. 

     

    Hope this helps.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Simon_King
    Simon_King Member

    Hi,

     

    I have tried this and even tried comparing lengths but beastmode does not seem to compute when there is an empty field.  when trying to use LENGTH it shows one cell has length 16 and the one next to it blank, where I would hope is sees empty as length 0.  Any ideas on how to see/select and empty cell?

  • MarkSnodgrass
    MarkSnodgrass Coach

    Are the values that are in "Value Owned" and "Managed Values" text or numeric?

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Simon_King
    Simon_King Member

    This is all text as it is actually domain names, its just that a blank entry does not seem to compute

     

  • Simon_King
    Simon_King Member
    Answer ✓

    What I have done is a change of logic - I am just discounting those which have a managed value (marked as Zero) and therefore this leaves those which do not have a corresponding value

     

    (CASE
    WHEN LENGTH(`ManagedDomains` )> 1
    THEN 0
    ELSE 1

    END)

     

    Once you get the logic its so simple ?

Categories