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)
)
Best 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 1END)
Once you get the logic its so simple ?
2
Answers
-
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 any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
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?
0 -
Are the values that are in "Value Owned" and "Managed Values" text or numeric?
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
This is all text as it is actually domain names, its just that a blank entry does not seem to compute
0 -
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 1END)
Once you get the logic its so simple ?
2
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive