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! Go to Solution.
I think you could do this :
dimension: unique_hash_substr {
type: string
sql: SUBSTRING({% parameter unique_hash %},28,8)
;;
}
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
}