This time I need your help about Field Filter.
I have a table (payout) that has a datetime column (created_at) in UTC. I need to create a dashboard that will be controlled by a date filter.
However, to get an accurate data shown by this dashboard I need to convert this created_at from UTC into local timezone. Therefore, I created this view (payout_view) and my SQL in Metabase is:
select count(*) as trx
from stars.payout_view where status = 'funding_received'
and date(created_at) = {{created_at}}
which {{created_at}} is the Field Filter and refer to a table in stars schema,
while my view query is:
create or replace view stars.payout_view as
with converted as (
select payout.id
, payout.user_id
, case when company.country_id = 19 then payout.created_at + interval '6 hours' else payout.created_at + interval '7 hours' end as created_at
, payout.status
from production.payout
left join production.company
on payout.company_id = company.id)
select * from converted
with no schema binding
So, when I run my SQL in metabase, I got this error ERROR: invalid reference to FROM-clause entry for table "stg_stars__date" Hint: There is an entry for table "stg_stars__date", but it cannot be referenced from this part of the query.
Hi @flamber
Apologies, I pasted outdated code.
I meant this code:
select count(*) as trx
from production.payout_view where status = 'funding_received'
and {{created_at}}
and the error is ERROR: invalid reference to FROM-clause entry for table "payout" Hint: There is an entry for table "payout", but it cannot be referenced from this part of the query.
@abed Make sure that you have selected the correct table and field it should reference in the sidebar, since it looks like you might have created the variable originally for a different table.
Hi @flamber, I was suspecting that as well. Then I tried another SQL and now it's not a view:
select * from
( select payout.id
, payout.user_id
, case when company.country_id = 19 then payout.created_at + interval '6 hours' else payout.created_at + interval '7 hours' end as created_at
, payout.status
from production.payout
left join production.company
on payout.company_id = company.id
) a
where {{created_at}}
and selected the reference column from a table of the same schema (production.payout.created_at) in the sidebar.
However, the error was still the same.
I would recommend that you check the query log on your database. It might help you understanding what is going wrong.
Metabase works with Views, just like regular tables.
Here is the result.
I created a view called payout_view which is formed by:
create view production.payout_view
as
select payout.id
, payout.user_id
, case when company.country_id = 19 then payout.created_at + interval '6 hours' else payout.created_at + interval '7 hours' end as created_at
, payout.status
from production.payout
left join production.company
on payout.company_id = company.id
with no schema binding
then in the Metabase, my query is:
select * from
production.payout_view
where {{created_at}}
I also selected the column from a table of the same schema as the query in view as the reference. Please see the screenshot below:
@abed But in the "Field to map to", it looks like you are referring to the actual Table and not the View.
It's difficult for me to see specifics, since you might have renamed the View in Metabase's Admin > Data Model.
Remember that if you have just created or changed structure, then make sure you sync via Admin > Databases > (db) > Sync database schema now.
@flamber
After selecting created_at from payout_view view as "Field to map to", it works fine!
Of course I had to sync the database manually first so that it would appear on the sidebar.
