Manipulating string in a filter

I want to filter my table based on a user input value and to achieve this I am using templated filter:

view: post_id_from_unique_hash {
       

  derived_table: {
             SQL:
                    SELECT _id
                    FROM "MONGODB"."PUBLIC"."POSTS"
                   WHERE  {% condition unique_hash %} uniquehash {% endcondition %}
                   GROUP BY 1  ;;
                            }
  
  filter: unique_hash {
    type: string
  }
  
  dimension: post_id {
    type: string
    sql: ${TABLE}."_ID" ;;
  }
  
  
    
}

The problem is the I want to manipulate the string the user will pass in the filter. For eg. in unique_hash the user would pass 'https://vote.flipfit.com/feed6186d0c98178b0' but I only want 

substring( 'https://vote.flipfit.com/feed6186d0c98178b0', 28,8) since only that value is stored in my DB as the unique hash. Any suggestions on how I can do this?

Solved Solved
0 2 275
1 ACCEPTED SOLUTION

JMJ1
Participant II

  I think you could do this :

  dimension: unique_hash_substr {
    type: string
    sql: SUBSTRING({% parameter unique_hash %},28,8)
      ;;
  }

View solution in original post

2 REPLIES 2

JMJ1
Participant II

  I think you could do this :

  dimension: unique_hash_substr {
    type: string
    sql: SUBSTRING({% parameter unique_hash %},28,8)
      ;;
  }

Thank you, this helps. I also found another way of doing this using a similar method as you suggested,

derived_table:

{

sql: SELECT _id

FROM "MONGODB"."PUBLIC"."POSTS"

WHERE __insert field__ = substring({% parameter unique_hash %}, 28, 😎 GROUP BY 1 ;;

}

parameter: unique_hash {

type: string

}

Top Labels in this Space
Top Solution Authors