Case in list - Tableau-CodePudding

I'm trying to filter out a huge amount of data out so i decided to create a calculated field and using case if product_id in the list then '1' else '0' but for some reason it though syntax error.

this is my calculated field:

CASE when product_id in ( '31049','31048','26166','27816','26031','28861','28864','28863','28203','28110','20641','38112','45174','20645','28404','20646','20648','26159','33287','31417','40551','41020','40550','40550','40553','40554','29804','29941','31430','33354','36730','26073','31432','31433','31431','38154','38166','26029','28341','45138','38069','42069','26060','26060','33886','33886','28392','29518','44879','20651','20655','42914','37535','28031','27588','29297','37688','37709','29551','29551','30183','29550','26187','29549','41348') THEN '1' ELSE '0' END

Any idea who it should be written?

Thanx in advance :)

CodePudding user response：

On a sample dataset this works:

SELECT RIDE_ID as ri,
CASE 
 WHEN ri in ('5EB0FAD625CFAEAB', '5A9314E3AF8DCC30') THEN '1'
 ELSE '0'
END AS result
FROM CITIBIKE_TRIPS LIMIT 10;

I get:

CodePudding user response：

yes it works in the database but not in Tableau :) I couldn't run it in a calculated field

Maybe using LATERAL would allow to run it from Tableau:

CREATE OR REPLACE TABLE t(ID  INT, product_id TEXT);
INSERT INTO t VALUES (1, '31049'),(2,'31048'), (3, '100');


SELECT *
FROM t
,LATERAL (SELECT CASE WHEN t.product_id IN ( '31049','31048','26166','27816'/*...*/) 
                      THEN '1' ELSE '0' END) AS s(result);