Pre-populating possible text variable values to display as a dropdown

What I’m trying to achieve is having a dropdown with values like day/week/month/year for the following part of my custom SQL query:

  date_trunc({{Interval}}, created_at)

I can set the variable to be text, but then I need to enter those values by hand. Would it be possible to somehow convert this to a dropdown with a set of pre-populated values?

Hi @pawel
You would have to create a table with the values and use a Field Filter on that table, joined with the query.
There’s a request open for what you’re asking for: - upvote by clicking :+1: on the first post

1 Like

Lucky you. If I try to run a similar query with {{my_variable}}, it says must appear in the GROUP BY clause or be used in an aggregate function.
If I directly enter ‘myvalue’, it works.

Actually found the specific issue for this context: - upvote by clicking :+1: on the first post

@abhilash2908 Which database are you querying? Please post your query, there’s workarounds for most databases.

It’s Vertica DB.

@abhilash2908 Well without seeing the query and since I don’t have a Vertica instance to test with, then I would guess something like this:

[[GROUP BY date_trunc({{Interval}}, created_at)]]

Or search the forum for “complex default value”, since that might be an option as well:

SELECT date_trunc({{revid}}, to_timestamp(s_{{clvidientId}}.engagementDetails_{{bid}}.cdate, 'yyMMddHHmmss'))  as cdate, 
sum(s_{{vid}}.engagementDetails_{{bid}}.re_int) AS "sum"
FROM s_{{vid}}.engagementDetails_{{bid}} 
WHERE ((cid = {{vid}}) [[AND t0.mid > {{mid}}]] AND (cdate BETWEEN {{startDate}} AND {{endDate}}))
GROUP BY date_trunc({{revid}}, to_timestamp(s_{{vid}}.engagementDetails_{{bid}}.cdate, 'yyMMddHHmmss'))
ORDER BY date_trunc({{revid}}, to_timestamp(s_{{vid}}.engagementDetails_{{bid}}.cdate, 'yyMMddHHmmss'))

[Vertica]VJDBC ERROR: Column “engagementDetails_2.cdate” must appear in the GROUP BY clause or be used in an aggregate function

If I replace {{revid}} with 'month', it works. But not otherwise.

I figured out the solution.
Replaced last 2 lines with

GROUP BY cdate
ORDER BY cdate
1 Like