Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

One date field for two filters #6947

Open
petar777 opened this issue Feb 13, 2018 · 9 comments

Comments

@petar777
Copy link

commented Feb 13, 2018

Hello,

Is there a way to do the following thing:

I am doing a SQL query where I join different tables which contain a date column. It looks like this:

select orders.sclient_name as "Name", orders.sclient_phone as "Номер", iname as "Product", outcalls.campaign_name as "Campaign" from orders inner join outcalls on orders.iparty_id = outcalls.iparty_id inner join items it on orders.iid = it.iid where {{DateB}} and {{DateC}}

Can I somehow add only one filter to my dashboard in order to set the date range in the tables at once?

where orders.datetime = {{DateA}} and outcalls.datetime = {{DateA}}

When I try to do something like the above mentioned I receive an error saying "No operator matches the given name and argument type(s). You may need to add explicit type casts."

Thank you in advance!

@lindsay-stevens

This comment has been minimized.

Copy link
Contributor

commented Feb 14, 2018

No operator matches the given name and argument type(s). You may need to add explicit type casts.

This looks like a postgres error. What it means is that it's trying to compare your orders.datetime column with the value supplied via Metabase in {{DateA}} but it can't do the comparison because they're not the same type - possibly Metabase is passing in a string, or some other type that isn't the same as the type as orders.datetime.

I think there's a postgres setting to allow implicit casts but it's better to follow the tip in the error and add an explicit type cast. For example if orders.datetime is a timestamp with timezone, you could do the explicit type cast either with SQL syntax: CAST({{DateA}} AS timestamptz) or postgres-specific syntax: {{DateA}}::timestamptz - note I didn't test either of these but it's generally how explicit type casts are done in postgres.

@salsakran

This comment has been minimized.

Copy link
Contributor

commented Feb 15, 2018

@petar777 What is the type of variable you are giving DateA?

@petar777

This comment has been minimized.

Copy link
Author

commented Feb 20, 2018

Sorry for the late response. @lindsay-stevens I tried doing what you mentioned but unfortunately it does not help. @salsakran it is a timestamp.

@JMGGarcia

This comment has been minimized.

Copy link

commented May 10, 2018

Hello,

Is there any update on this? In my case, I want to compute the ratio of one metric over another that are in two different tables over a given period of time. I can't really have them in the same table since these metrics are aggregated in different ways.

@petar777

This comment has been minimized.

Copy link
Author

commented May 11, 2018

@JMGGarcia I found a way around this that worked for me. If you want set to set the date of one table to be equal to a metabase variable which is from another one you can do it like so:

SELECT *
FROM table_A
WHERE cast(table_A.date_value AS date // hour // whatever you need) IN (
 SELECT cast(table_B.date_value AS date // hour // whatever you need
          FROM table_B
          WHERE {{table_B.date_value}})

I hope it helps :)

@JMGGarcia

This comment has been minimized.

Copy link

commented May 11, 2018

@petar777 Thanks for the help! Your solution works in the case the all the dates in table_A appear in table_B, which is not always the case for my problem. Either way, if there is no better solution, I can always add these values to table_B (or a new table just for this purpose). Thanks!

@petar777

This comment has been minimized.

Copy link
Author

commented May 11, 2018

@JMGGarcia if you can explain your problem in further detail I might help.

@JMGGarcia

This comment has been minimized.

Copy link

commented May 11, 2018

@petar777 Imagine I have two tables (tA and tB) that aggregate the number of times sone events (say eA and eB) happen by the hour. So tA might look like:

timestamp           | total_eA 
-------------------------------
01-01-2000T01:00:00 |        5
01-01-2000T02:00:00 |        6
01-01-2000T04:00:00 |        1
...

And tB:

timestamp           | total_eB 
-------------------------------
01-01-2000T01:00:00 |       23
01-01-2000T03:00:00 |       46
01-01-2000T04:00:00 |       13
...

And I basically want sum(total_eA) / sum(total_eB) within a given time interval. Since I don't store hours where there are no events, following your solution, hours that appear in one table and not in the other might not count for the total (i.e. they are ignored by the IN operator). Hopefully I was clear on what the problem was.

@JoaquinMontesinos

This comment has been minimized.

Copy link

commented Jan 17, 2019

@JMGGarcia @petar777 Did you solve this issue? I have same problem. I am trying to use one filter to both tables. It works when I use 1 filter value but not for various values. I have not idea how to solve this problem, could you explain me how did you solve it?
I am using subqueries.

SELECT (b.count_one/b.count_two) as CPI, b.count_two, b.count_one, b.M_S
FROM
(SELECT count(table1.Name) as count_two , a.count_one, a.M_S
FROM table1,
(SELECT sum(table2.Cost) as count_one, M_S
FROM table2
WHERE 1=1
[[AND {{filter}}]]
)a
WHERE 1=1
[[AND table1.M_S = a.M_S]]
)b

I thought to use two filters and equalize 1 of them with the other, using at really only one for the filtering, but it give me errors.
[[AND {{filter2}} = {{filter}}]]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
5 participants
You can’t perform that action at this time.