CODEWITHSUNDEEP
sqltableau-api
QuicKick
QuicKick

Reputation: 65

How to write an IF statement in SQL to hide the non duplicated row

Thank you for looking into this, and much is appreciated.

I would like to have an additional column called "SHOW_or_HIDE" where I can add the filter feature in Tableau.

Below is my SQL table for my Tableau and I want to create an IF statement base on if the same quarters has BOTH types "POR" AND "CWV" then I want to categorize it "SHOW" BUT if it only shows ONE either POR or CWV in a quarter then "HIDE". Basically, I only want my tableau bar chart to show a full quarter with POR & CWV only and if the quarter has only ONE of either POR or CWV I would like to hide but still have an option to show them in Tableau.

I'm extremely new to SQL so I'm having difficulty on how to nest it also.

Current Table

SCENARIO TYPE QUARTER PROGRAM GEO UNITS SHOW or HIDE
(4) FY20-Q1_POR POR FY20-Q1 XYZ USA 2 HIDE
(3) FY20-Q2_CWV CWV FY20-Q2 XYZ USA 1 show
(4) FY20-Q2_POR POR FY20-Q2 XYZ USA 5 show
(3) FY20-Q3_CWV CWV FY20-Q3 XYZ USA 3 show
(4) FY20-Q3_POR POR FY20-Q3 XYZ USA 4 show
(3) FY20-Q4_CWV CWV FY20-Q4 XYZ USA 9 HIDE
(3) FY21-Q1_CWV CWV FY21-Q1 XYZ USA 1 show
(4) FY21-Q1_POR POR FY21-Q1 XYZ USA 1 show
etc 
#legacy sql
SELECT CASE WHEN TYPE ='CWV' THEN CONCAT('(3) ',QUARTER,'_',TYPE) 
    WHEN TYPE ='POR' THEN CONCAT('(4) ',QUARTER,'_',TYPE) 
    END AS SCENARIO
    , CASE WHEN TYPE = 'ACTL' THEN 'ACTUALS'
            WHEN TYPE = 'OTLK' THEN 'OUTLOOK'
            ELSE TYPE END AS TYPE
    , QUARTER 
    , BC.PROGRAM AS PROGRAM 
    ,  CASE WHEN GEO = 'Europe' THEN 'EUROPE' 
         WHEN GEO = 'India' THEN 'Pan India'
         WHEN GEO = 'LATAM' THEN 'LA'
      ELSE GEO END GEO
    , FLOAT(SUM(CA)) AS UNITS

    ***, #--> ?? HERE: my guess is in this line is where I would put the coding?? AS "SHOW_or_HIDE"***

  FROM [xxxxxxxxxxxxxxxxxxxxxxxxxxx.BC_REPORTS] BC

where PROGRAM = 'XYZ'    #these are my temporary filters for easier viewing
and TYPE <>'ACTL'        #these are my temporary filters for easier viewing
and TYPE <>'OTLK'        #these are my temporary filters for easier viewing
and GEO = 'NA'           #these are my temporary filters for easier viewing
and CUSTOMER = 'xyz'     #these are my temporary filters for easier viewing  

group by  1,2,3,4,5
order by 3,2





[![enter image description here][1]][1]


  [1]: https://i.sstatic.net/xRotX.png

Upvotes: 0

Views: 45

Answers (1)

user8103263
user8103263

Reputation:

Might not be the best solution.

But the idea is to LEFT OUTER JOIN on distinct quarters for each type and then check for NULL values.

Written in MS Access, so the actual SQL might slightly differ (iif function).

select 
    iif (type = 'CWV', '(3) ' + bc.quarter + '_' + type, iif(type = 'POR', '(4) ' + bc.quarter + '_' + type, null)) as scenario,
    type,
    bc.quarter,
    program,
    geo,
    sum(ca) as units,
    iif(cw.quarter is not null and por.quarter is not null, 'SHOW', 'HIDE') as show_or_hide
from (
    bc_reports as bc 
    left outer join (select distinct quarter from bc_reports where type = 'CWV') cw on bc.quarter = cw.quarter
)
left outer join (select distinct quarter from bc_reports where type = 'POR') por on bc.quarter = por.quarter
group by bc.quarter, type, program, geo, cw.quarter, por.quarter
order by 3, 2

Output

Upvotes: 1

Related Questions