Beast Mode Syntax Questions
Hello All,
I am having some issues with my BeastMode.
In plain language as I can muster here is what I am trying to accomplish. I am trying to have a logical test run two different calculation methods in the same beast mode.
Logically I want it to do the following
IF ID = 1
THEN ( ###ID ONE CODE###)
ELSE( ###ID TWO CODE####)
I just can't quite get the syntax right. Each Piece runs individually, but not together.
Here is the beast mode
(CASE WHEN `Dispenser_Number`= 'xxxx'
(COUNT(DISTINCT(CASE
WHEN `NetRevenue` > '0' AND `Purchase Category` = 'OBJECT'
THEN `Contact_ID`))))
ELSE(
(COUNT(DISTINCT(CASE
WHEN `Procedure_1` = 'METRIC' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
WHEN `Procedure_2` = 'METRIC' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
WHEN `Procedure_3` = 'METRIC' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
WHEN `Procedure_1` = 'Previous TNS' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
WHEN `Procedure_2` = 'Previous TNS' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
WHEN `Procedure_3` = 'Previous TNS' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
))))
END)
--edited for personal and identifying info
Comments
-
Few questions:
1. You're wanting unique counts of those contact IDs?
2. Why does it look like you have 3 case statements in this, why not do everything in one?
I'd write this up something like this, but because of your business rules or whatever, this may not work...but may help spur a better idea.
(COUNT(DISTINCT(
CASE
WHEN `Dispenser_Number`= 'xxxx' and `NetRevenue` > '0' AND `Purchase Category` = 'OBJECT' THEN `Contact_ID`
WHEN `Procedure_1` = 'METRIC' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
WHEN `Procedure_2` = 'METRIC' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
WHEN `Procedure_3` = 'METRIC' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
WHEN `Procedure_1` = 'Previous TNS' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
WHEN `Procedure_2` = 'Previous TNS' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
WHEN `Procedure_3` = 'Previous TNS' AND `Outcome_Desc` = 'Aid Sale' THEN `Contact_ID`
END)))1 -
Thanks for the help.
1. Yup you got it 100% I am counting each unique instance.
2. This is more complicated.
Full disclosure we are moving from one management system and need the cards to be flexible enough to use both data mappings. The data from both systems have overlapping terms. I was trying to ensure that nothing is counted more than once. I edited OBJECT and METRIC because usign the real ones would reveal some personal info. The issue is that OBJECT and METRIC have overlapping terms I can't change without breaking things.
I am still working on and off on this. See below for a new iteration. I am trying to implement your idea, validation takes a while due to constraints.
(CASE WHEN `Dispenser_Number`= 'XXXX'
THEN
(COUNT(DISTINCT(CASE
WHEN `NetRevenue` > '0' AND `Purchase Category` = 'OBJECT'
THEN `Contact_ID`)))
ELSE
(COUNT(DISTINCT(CASE
WHEN `Procedure_1` = 'METRIC' AND `Outcome_Desc` = 'Aid Sale'
THEN `Contact_ID`
WHEN `Procedure_2` = 'METRIC' AND `Outcome_Desc` = 'Aid Sale'
THEN `Contact_ID`
WHEN `Procedure_3` = 'METRIC' AND `Outcome_Desc` = 'Aid Sale'
THEN `Contact_ID`
WHEN `Procedure_1` = 'Previous TNS' AND `Outcome_Desc` = 'Aid Sale'
THEN `Contact_ID`
WHEN `Procedure_2` = 'Previous TNS' AND `Outcome_Desc` = 'Aid Sale'
THEN `Contact_ID`
WHEN `Procedure_3` = 'Previous TNS' AND `Outcome_Desc` = 'Aid Sale'
THEN `Contact_ID`
)))
END)I made some small changes and I am still getting a syntax error.
0 -
I'll try to look at this one a bit more closely later today, but as I quickly look at it...you have 3 CASE statements with only 1 End. they have to be 1:1.
Also, again I dont know your data, but it may be helpful to take a step back in the process if you can and do something in ETL to help join the data so your terms/metrics line up more easily. But that may be a non-starter.
1 -
Wow,
Thank you so much for everything. I feel really silly. I will make sure to keep CASE and END 1:1.
I will keep in mind what you said about taking a step back and maybe restructuring some stuff.
I will mark something as a solution as soon as I can validate.
0 -
Don't feel silly, no reason for that, you're working on a tough BM w/ case statements...nothing to be silly about. I once opened a support ticket b/c I was moving too fast and forgot to put "select" in mysql query...when you do that you can then feel silly.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 708 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