I work with a lot of dirty user entered data.
I would like to try to bucket all of my results into a cleaner report. Something like:
Report all records where vendor_name contains ‘Agilent’ as ‘Agilent Technologies’.
I can ask for all of the iterations, but I’m having trouble bucketing all of the results into an " as " statement. Is there way to build a customer filter or alter my report?
Hi
The best way to do that would be to create a new dimension with a custom SQL CASE WHEN statement to define your bucketing. The easiest way to do that would be to use SQL like parameter.
It would be something like this:
dimension: clean_name { type: string sql: CASE WHEN ${dirty_name} like '%agilent%' THEN 'Agilent' WHEN ${dirty_name} like '%something%' THEN 'Something Else' ELSE ${dirty_name} END;; }
Of course depending on the syntax you use, the exact query might differ insignificantly. You can also add as many buckets as you’d like to your CASE WHEN statement by adding other conditions.
Thanks for your help, Aleks! I didn’t consider defining an underlying dimension from the get go. Makes sense!!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
