Compare to text lists and highlight difference

Reply
Highlighted
Orange Belt

Compare to text lists and highlight difference

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)
)


Accepted Solutions
Highlighted
Orange Belt

Re: Compare to text lists and highlight difference

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 Smiley Happy

View solution in original post


All Replies
Highlighted
Red Belt

Re: Compare to text lists and highlight difference

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.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Highlighted
Orange Belt

Re: Compare to text lists and highlight difference

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?

Highlighted
Red Belt

Re: Compare to text lists and highlight difference

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




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Highlighted
Orange Belt

Re: Compare to text lists and highlight difference

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

 

Highlighted
Orange Belt

Re: Compare to text lists and highlight difference

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 Smiley Happy

View solution in original post

Announcements
Win free lodging at Domoapalooza! We want you to share you favorite Domo tips and tricks. Click here for more details!