cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member
I came across an interesting use case with a customer where they had a name and ID column on a dataset: 
  • Select a filter value for name, and not id -> show results for name
  • Select a filter value for id, and not name -> show results for id
  • If values are selected for both name and id -> show results that satisfy either filter condition.
Using a simple
where [id_column=id_filter] and [name_column=name_filter]
doesn't achieve the above behavior, especially since we want to show results that satisfy either condition if there are selections in both the id and name filters.
 
Instead, we want to leverage default filter notation and direct replacement filters in a case when statement:
SELECT *
FROM table
WHERE
CASE WHEN 'default' IN ('[name_filter|default]') THEN [id_column=id_filter]
WHEN 'default' IN ('[id_filter|default]') THEN [name_column=name_filter]
ELSE [id_column=id_filter] OR [name_column=name_filter] END
This SQL tells Sisense to use the id column if no value is passed in name_filter (and hence it returns 'default') and vice versa. If values are applied to both the name and id columns, the else clause is applied.
Version history
Last update:
‎10-26-2021 02:37 AM
Updated by:
Contributors
Community Toolbox

Recommended quick links to assist you in optimizing your community experience:

Need additional support?:

Community Support Request