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!

Hi - please don’t link to images, add all information to your question as editable text. Can you also update your question to provide sample data and the result you want to achieve? Regarding your CASE/WHEN question - what did the documentation say when you read it and therefore what, precisely, don’t you understanding? — NickW, Jul 20 '23 at 16:10
FYI - I can't see the image (because imgur.com is blocked) - so it really isn't a great idea to rely on images when there is a text alternative — Paul Maxwell, Jul 21 '23 at 02:09

score 0 · Answer 1 · answered Jul 21 '23 at 02:05

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.

Converting Tableau statement to SQL query for calculations

1 Answers1