Converting Tableau statement to SQL query for calculations

Question

Good mortning,

I tried to conver the statement in Tableau in SQL server, but I received an error.Could you help me how to return 2 values in the CASE statement in SQL based on certain criteria and multiply them to get the final computed numeri value? When I used multiplying symbol in the CASE statement, I received an error shown as below.

Tableau Statement

IF ([CodingChange] ='Yes' OR [QueryOpportunity]='Yes')  THEN [ActualLift] END * IF ZN([!BaseRate]) > 0 THEN [!BaseRate] END

What I tried in SQL server

[Obtained Lift -] AS
CASE
    WHEN ([CodingChange] ='Yes' OR [QueryOpportunity]='Yes')  
    THEN [ActualLift] 
    * WHEN [!BaseRate] > 0      
    THEN [!BaseRate] 
END;

P.S. Can I also know if I'm supposed to use "WHEN" multiple times in the CASE statement in SQL when I meant to use "ELIF" in Tableau?

I appreciate your help!

Answer 1

The second IF in the original formula is really there to stop a multiplication by zero, so just include testing for that condition prior to the multiplication.

CASE 
    WHEN ([CodingChange] ='Yes' OR [QueryOpportunity]='Yes') AND [BaseRate] > 0 
        THEN [ActualLift] * [BaseRate] 
    -- ELSE NULL --??
END

What the original formula doesn't reveal is what happens if [BaseRate] is zero. Not sure why there is a problem though, anything multiplied by zero is zero. Maybe the formula approach simply assumes the report shows nothing if the BaseRate is 0 so you may want to return NULL - not sure on that point.

nb note sure why you are using square brackets [ ] - is the database SQL Server? You could remove them as no column name uses a space or special character.