LinkedIn
Twitter
Knowledge Drop
Last tested: Sep 7, 2019
Say you want to filter a column by multiple values in a "sub column", i.e you have a column order id's and a column product id's, and you want to filter for order id's that contain product id A AND product id B.
The issue: You can't use a custom filter because that filter is a row based filter and won't account for multiple row values of product id for one value of order id.
The bad solution: You can use a PDT to filter on product id A then filter on product id B in the explore (or in a derived table with the filter for A as a sub select)
The better solution: A group concatenation aggregate function! How this works: The aggregate function works like any other function and will concat values based on the grouping. Implementation: We can either use
Approach 1: Create measure type list
measure: product_id_list {
type: list
list_field: product_id;;
}
Approach 2: Create measure of type string and use appropriate sql dialect for group_concat
measure: group_concat_product_id {
type: string
sql: group_concat(${product_id}) ;;
}
Note: The group_concat function varies by dialect.
Once you create the measure, you can then select orders in your explore and then apply a custom filter to 'group_concat_product_id' like so:
${view_name.group_concat_product_id} contains(A) AND
${view_name.group_concat_product_id} contains(B)
One thing to keep in mind that the measure just concats all id's together which could cause incorrect results, i.e. if you have a product_id that is "ABC", the above filter wont filter out that value. This can be solved by using a separator (i.e. a comma) and including that separator in the filter.
This content is subject to limited support.
