| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
1 | Go to File > Make a Copy to get started | |||||||||
2 | Quick CASE for GDS | Made by @hannahrampton | ||||||||
3 | FIELD TO MATCH | ELSE | To copy the below, click INTO the cell and copy the contents rather than copy the cell itself. This avoids quote marks being added around your CASE statement. | |||||||
4 | Query | Other | " | |||||||
5 | ||||||||||
6 | MATCH TO (SEPARATE BY | ) | LABEL | CASE | |||||||
7 | 1 | youtube|you tube | YouTube | CASE WHEN REGEXP_MATCH(Query, '.*youtube.*|.*you tube.*') THEN 'YouTube' WHEN REGEXP_MATCH(Query, '.*shirt.*|.*hood.*') THEN 'Tops' WHEN REGEXP_MATCH(Query, '.*sunglass.*|.*hat .*|.* cap.*') THEN 'Head' WHEN REGEXP_MATCH(Query, '.*bike.*') THEN 'Wheels' WHEN REGEXP_MATCH(Query, '.*bottle.*|.*backpack.*|.*bag .*') THEN 'Extras' ELSE "Other" END | WHEN REGEXP_MATCH(Query, '.*youtube.*|.*you tube.*') THEN 'YouTube' | |||||
8 | 2 | shirt|hood | Tops | WHEN REGEXP_MATCH(Query, '.*shirt.*|.*hood.*') THEN 'Tops' | ||||||
9 | 3 | sunglass|hat | cap | Head | WHEN REGEXP_MATCH(Query, '.*sunglass.*|.*hat .*|.* cap.*') THEN 'Head' | ||||||
10 | 4 | bike | Wheels | WHEN REGEXP_MATCH(Query, '.*bike.*') THEN 'Wheels' | ||||||
11 | 5 | bottle|backpack|bag | Extras | WHEN REGEXP_MATCH(Query, '.*bottle.*|.*backpack.*|.*bag .*') THEN 'Extras' | ||||||
12 | 6 | |||||||||
13 | 7 | |||||||||
14 | 8 | |||||||||
15 | 9 | |||||||||
16 | 10 | |||||||||
17 | ELSE "Other" | |||||||||
18 | END | |||||||||
19 | ||||||||||
20 | Released to support 'Data Studio UI - CASE and Treemap filters' (22/06/20) | CASE WHEN REGEXP_MATCH(Query, '.*youtube.*|.*you tube.*') THEN 'YouTube' WHEN REGEXP_MATCH(Query, '.*shirt.*|.*hood.*') THEN 'Tops' WHEN REGEXP_MATCH(Query, '.*sunglass.*|.*hat .*|.* cap.*') THEN 'Head' WHEN REGEXP_MATCH(Query, '.*bike.*') THEN 'Wheels' WHEN REGEXP_MATCH(Query, '.*bottle.*|.*backpack.*|.*bag .*') THEN 'Extras' ELSE "Other" END | ||||||||
21 | https://www.hannahrampton.co.uk/data-studio-filter-with-case-and-treemaps/ | |||||||||
22 | ||||||||||