Beast Mode Syntax Questions

achirico

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

 

NewsomSolutions

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

 

 

 

achirico

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. 

 

 

 

NewsomSolutions

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.

 

 

achirico

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. 

NewsomSolutions

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.