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

 

Tagged:

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

     

     

     

  • 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. 

     

     

     

  • 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.

     

     

  • 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. 

  • 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.

     

     

This discussion has been closed.