Filter Field with LIKE based on another Field

Hi all!

Hopefully someone is able to understand what I'm trying to achieve and help with a solution.

 

I need to filter based on a "Tag" field with single text values, once selected would filter all relevant items in another dataset that has these tags in a single field "Tags" with multiple text comma delimited "tag1, tag2" for example.

 

Both created as a new beast mode "=tag_filtering" in both datasets to allow filtering.

Tag - "red" (used to filter)

Tags - "red,small" (needed to filter by)

 

My thought was a using beast mode something as below but have a feeling I may be well off, especially without having a wildcard % as part of the formula but having no joy, is this possible?

 

CASE WHEN `tag` LIKE `tags` THEN `GROUP_CONCAT(pt.tag)` END

 

Any input into the right direction is very much appreciated.

 

Thanks

Simon

 

Comments

  • Corrected mistake in above, CASE WHEN `tag` LIKE `tags` THEN `tags` END

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    @Simon_UK  i've approached this problem several ways, applicability is dependent on your requirements.

     

    My assumptions. 

    1) you have a lookup table with a field Tag containing a list of tags (RED, BLUE, GREEN)

    2) your transactions have a column Tags with a semicolon delimited value containing tags.

     

    GOAL

    Use a checkbox or radio box filter card to select 'all transactions that match a tag'.

     

    Approach 0) 

    use a filter card on the transaction tags column you can type to filter, so as you type 'red' all the transactions containing red will show up ... then you click.  not great.... but it works without any extra effort or row duplication.  not the best user experience.  (do make sure you have the version of the checkbox filter card that allows you to type to filter, if you don't contact your CSM).

     

    Approach 1)

    JOIN Lookup to Transactions on a %LIKE%  clause in ETL.  This may cause row duplication if multiple tags from the lookup match your transaction table, but it will give you the ability to filter on individual values.

     

    Approach 2)

    Build a custom app

  • Thanks Jae, appreciate your details response and suggestions.

     

    I opted for the easiest/quickest way, specifying each tag individually as its own case.

    e.g

    CASE WHEN `tags` LIKE '%tag1%' THEN 'tag1' END

     

    Many Thanks

    Simon