I’m trying to write a query that involves multiple tables but it seems like it’s not possible due to the nature of Field Filter being tied to a table’s column. Is there a way to refer to a Field Filter’s from and to directly in the query? I want to use a Field Filter so I can use the same dashboard filter as my other queries, so having two Date variables are not an option for me. That option sounds like a hack.
The query is basically:
SELECT
(SELECT SUM(field) FROM table_a WHERE {{date}}) /
(SELECT SUM(field) FROM table_b WHERE {{date}})
I feel like the field filter should look more like {{date:table_a.created_at}} so it can be referred to different fields in one query and generated properly. Maybe I don’t know how to use this. Please help!
There are a lot of upvotes on that issue sir! Do you have any idea when they plan to add alias support?
I saw your response about a lookup table in some other places. I don't have write access to the database I'm querying - can you think of any other workaround?
One of the workaround I've just come up with is to add an extra inner join with one of the tables on a date condition.
context: I have a cte called table1 containing from multiple tables, and I'd like to apply the same date for each of them, then aggregate values. so I add an unnecessary join just to attach the table2 and return distinct values only from the table1 as there will be lots of duplications.
select bank, sum(credit)
from (
select distinct t.bank, t.trans_datetime, t.credit
from table1 t
join table2
on extract(month from t.trans_datetime) = extract(month from table2.trans_datetime)
and extract(year from t.trans_datetime) = extract(year from table2.trans_datetime)
where {{ month }}
) temp
group by bank;
on the first post