OR statements in card filtering

sattler360
sattler360 Member
in Magic ETL

Hi folks - We have a dataset where users can have datapointA and datapointB. We want to build a card where an end user can have a filter that asks for any user has datapointA = 'green' OR 'databpointB = 'circle'.

When we put those two datasets in a dataflow/card and add the datapoints as Quick Filters, it automatically assumes we want datapointA = 'green' AND 'databpointB = 'circle'.

How do we build quick filter conditions that assume ORs?

Comments

  • TheLookout
    TheLookout Contributor

    I don't know of a great solution here if you are only able to use Quick Filters. You could build a beastmode but that isn't great.

    CASE WHEN `datapointA` = 'green' OR `datapointB` = 'circle' THEN 'Yes' ELSE 'No' END

    The issue with this is you would need a separate beastmode for each combination of ORs.

    Something else you might try is combining the columns together.

    CONCAT(`datapointA`,'|',`datapointB`)

    This would give you a list of all unique ORs you might want. This issue here is you would need to select multiple values in the Quick Filter but this would allow you to pick any values in either column.

    Your quick filter might look something like this.

    blue|circle

    green|circle

    green|square

    orange|square

    etc

    Then you would select all values with 'green' in the first half and all options with 'circle' in the second half. If you go this route I'd recommend ensuring there are no NULL values in these fields as any NULL value in a CONCAT causes the whole thing to return NULL.

    CONCAT(IFNULL(NULLIF(`datapointA`,''),'N/A'),'|',IFNULL(NULLIF(`datapointB`,''),'N/A'))

    This would turn any blank or NULL values in either column into 'N/A'.

Categories