How to compare values in two columns in custom filters

Hey! 

I faced this issue, when I need to compare every value from one column with every value in the second column and filter the table.. Basically I have two columns with some values:

col1 col2
val1 vala
val2 valb
val3 valc
... ...

I need to compare: 

val1 with vala

val1 with valb

val1 with valc

val2 with vala

val2 with valb

and etc.

And I need to filter the table and leave only values, that are matching. 

I’d appreciate your help!

1 4 4,594
4 REPLIES 4

JMJ1
Participant II

You can create a dimension type yesno, and use it as filter. For example :

dimension: filter_match {type: yesno sql: ${TABLE}.col1=${TABLE}.col2;;}

You can create a dimension type yesno, and use it as filter. For example :

dimension: filter_match {type: yesno sql: ${TABLE}.col1=${TABLE}.col2;;}

Hey! Thanks for the answer! 

I guess I forgot to mentioned, that col1 and col2 are from two different views

JMJ1
Participant II

You could to define a explore of the main view and the join with the another view. After that you could define a dimension type yesno. For example:

in the model:

explore: view_A {

  join: view_B{
    relationship: many_to_one
    sql_on: ${view_A.????} = ${view_B.????};;
  }

in the view_A:

dimension: filter_match {type: yesno sql: ${TABLE}.col1=${view_B.col2};;}

minerkt
Participant IV

Hi @ayaroshenko 

I think you could also do a custom dimension within the explore that compares the two values.  Once you have a resulting column (T/F) you should be able to filter on it.

Top Labels in this Space
Top Solution Authors