Compare to text lists and highlight difference

Simon_King

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

Simon_King

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 ?

MarkSnodgrass

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.

Simon_King

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

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

Simon_King

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

 

Simon_King

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 ?