Best Of

You'd need to do a cartesian join on your dataset with your mapping dataset, apply the rule, and then reduce your dataset back to the original size.

Start by adding a new column called Join Column with a value of 1 to each your input dataset and your mapping dataset.

Join these two datasets together on the new join column.

Use a formula tile to calculate the new grouping:

CASE WHEN `insurance_company` LIKE `SearchString` THEN `Reclass` END

Then feed that into a group by, grouping by your identifier field and selecting the max of the formula to get the actual mapping.

Take that output and join it back to your original dataset.

@WorldWarHulk keep in mind that you're basically doing a cross join for the tables, so for example if in the original table you had an entry with a date of 7/5/2021, this will appear once for each exclusion period you have in your other table, the one you shared a screenshot of, and the filter will only remove the entry for the period Jun 23 2023 - Jul 6, 2023, still leaving the others in.

If the idea is not to have repeated entries, but only remove from the original dataset those where the date field falls into any of the exclusion periods you have defined, you'll need to do a couple of extra steps after the cross join.

1. Add a Formula Tile that will identify if the date falls between the exclusion period:
   CASE WHEN date >= Wipe Date (BOQ) AND date <= Forecast Due Date THEN 1 ELSE 0 END
2. Add a Group Tile that will include all the columns from your Original table and do either a SUM or MAX on the formula field we just created. This will return your dataset to it's original size, but now you'll have a flag that tells you whether that entry date falls under any of the given exclusion periods.
3. Now do the filter based on this rolled up flag being 0, so you are keeping only the entries that do not fall under any exclusion period.
4. Use an Output Tile to write it to a new dataset you'll use for your cards.

Hope this helps.

CASE WHEN REGEXP_LIKE(`field`, '^(A\d+).*$') THEN 
  REGEXP_REPLACE(`field`, '^(A\d+).*$', '$1')
ELSE
'unassigned'
END

You can use a formula tile and a regular expression to pull out the values which start with A followed by some numbers.

^ - Start of the line

() - Match group to store the results found - in this case the first match group which is stored in the variable $1

A - the literal letter A

\d+ - One or more digits

.* 0 or more of anything

$ - The end of the string