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

Question

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

question from:https://stackoverflow.com/questions/65925385/how-to-write-an-if-statement-in-sql-to-hide-the-non-duplicated-row

Answer 1

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