-1

I'm currently working on a project that would be super enhanced if I can get this SQL right! I want to SELECT various variables surrounding vehicles to represent as a table in Tableau, but when creating a calculated field (in tableau) and using If-then conditional format, the THEN options aren't all being displayed within the graphic and some results are even being incorrectly represented by certain options!

For example:

The below skeleton statement creates a field called MissingNumber wherein if "-----1" is true then MissingNumber will take the value 'Number 1'.

          If ------1 Then 'Number 1'
             ELSEIF
          If ------2 Then 'Number 2'
             ELSEIF
          If ------3 Then 'Number 3'
             ELSEIF
          If ------4 Then 'Number 4'
             ELSE 'Number 5'
          END

Continuing with the above format as guide, 'Number 1', 'Number 2', and 'Number 5' are represented categorically but NOT 'Number 3' or 'Number 4' even though I know that the data satisfies 'Number 3' and 'Number 4' too in quite a few records!

I believe that there is an issue with order here (or some other unforeseen problem) so I tried using a case statement: 

    CASE 
         WHEN (Rtrim((v.plt_no)) is null) or (Rtrim(v.plt_no = '')) THEN "Plate Only" 

         WHEN (v.rnw_dt is null) THEN "Expiration Only"

         WHEN ((DocumentTemplate is null) or (DocumentTemplate = '')) THEN "Registration Only"

         WHEN (Rtrim((v.plt_no)) is null or Rtrim(v.plt_no = '')) AND (v.rnw_dt is null) THEN "Plate & Expiration"

         WHEN (Rtrim((v.plt_no)) is null or Rtrim(v.plt_no = '')) AND ((DocumentTemplate is null) or (DocumentTemplate = '')) THEN "Plate & Registration"

         WHEN (v.rnw_dt is null) AND ((DocumentTemplate is null) or (DocumentTemplate = '')) THEN "Expiration & Registration"

    ELSE "All Good"

END AS MissingInfo

My QUESTION IS: What advice does the community have about whether I should continue trying the IF-Then format within tableau, or building the variable Missing Info within SQL?

ALSO with the suggested choice, how can I affect the code such that ALL options are correctly represented by a crosstab ("MissingInfo" on row side, "License Plate State" on column side). I have linked a snip of the code and a cross-tab example for reference! Please let me know if there will be anything else I'll need to include to make this more clear! **NOTE in the Tableau image "Plate Only" does NOT show up on the graphic!

Code-SNIP

Tableau-CrossTab

ResultSet for 'Plate Only'test

--The results are unexpected because plt_no is missing (what we want) but the document template field is NULL in 3 records. In that case, the 3 NULLs in the DocumentTemplate field should qualify those records as 'Plate and Registration Only' within the MissingInfo variable

Sam M
  • 4,136
  • 4
  • 29
  • 42
SirDevo100
  • 1
  • 2
  • _do not use this tag, use [tag:switch-statement]._ – jarlh Sep 27 '18 at 14:11
  • Those are `case` _expressions_... – jarlh Sep 27 '18 at 14:11
  • Thanks for your help.... – SirDevo100 Sep 27 '18 at 15:09
  • 1
    First, your image of the calculated field logic does not have a line for "Plate and Registration Only" as specified in the last sentence of your posting. Second, the logic order of operations in the first IF statement is a bit unclear. You can make it clearer by putting parenthesis around the OR and AND statements to better document what order you want the boolean expressions evaluated. – Sam M Oct 08 '18 at 09:08

1 Answers1

0

You have (basically)

"condition1 OR condition2 AND condition3"

which is evaluated as

"condition1 OR ( condition2 AND condition3 )".

Add parentheses to make that

"( condition1 OR condition2 ) AND condition3" .

Personally, when I have a combination of AND and OR in a condition, I always use parentheses to make the evaluation order clear, even if they are not strictly required.

Hans Kesting
  • 38,117
  • 9
  • 79
  • 111