Knowledge Drop

Filter a column on multiple values from another column


Userlevel 2

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.

Guru image.png

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.                

 

 


0 replies

Be the first to reply!

Reply