xrmx
commented
6 years ago You have to post the full stacktrace
Closed kartikshanbhag3333 closed 6 years ago
xrmx
commented
6 years ago You have to post the full stacktrace
kartikshanbhag3333
commented
6 years ago @xrmx Sorry for the trouble, but I have fixed it by using Jinja Templating, Following is the code for your or anyone's reference. Let me know the thoughts on this.
SELECT COUNT(DISTINCT userId)/
(SELECT COUNT(DISTINCT userId)
FROM AppUsageFactSuperset
WHERE 0=0
{% set filters = form_data.get('filters') %}
{% if filters %}
{% for eachFilter in filters %}
{% if eachFilter %}
{% set column_name = eachFilter.get('col') %}
{% set values = eachFilter.get('val') %}
{% set operator = eachFilter.get('op') %}
{% set joined_values = "'" + "','".join(values) + "'" %}
AND {{ column_name }} {{ operator }} ( {{ joined_values }} )
{% endif %}
{% endfor %}
{% endif %}
) AS `Reach_Temp`
FROM smartmeter_pbi_dot11.`AppUsageFactSuperset`
WHERE 0 = 0
{% set filters = form_data.get('filters') %}
{% if filters %}
{% for eachFilter in filters %}
{% if eachFilter %}
{% set column_name = eachFilter.get('col') %}
{% set values = eachFilter.get('val') %}
{% set operator = eachFilter.get('op') %}
{% set joined_values = "'" + "','".join(values) + "'" %}
AND {{ column_name }} {{ operator }} ( {{ joined_values }} )
{% endif %}
{% endfor %}
{% endif %}
ORDER BY `Reach_Temp` DESC
LIMIT 50000
Also, The tool is amazing, Thank you for creating an amazing tool for Analytics and Viz.
saurabh1-singh
commented
4 years ago Hey @kartikshanbhag3333 can you help me it in this, I am too trying to use jinja template but so far have not succeeded. The problem is i create a view just like yours but when i apply filters the values are filled correctly but it also tries to apply the filter after wrapping up the query in an query.
somewhat like this select from (select from abc where {filter_values}) as expr where {filter_values}
How can I avoid the outer filters
abhishekkumaresan
commented
4 years ago @xrmx Hi, My SQL query for a chart involves subqueries, I need the filter values chosen in the filter box (in the dashboard) in the subquery also, can I pass it by any means like Jinja templating, pls explain.
I understood it with https://github.com/apache/incubator-superset/issues/8183#issuecomment-536097091 thank you, sorry to disturb .
Make sure these boxes are checked before submitting your issue - thank you!
Superset version
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Superset 0.22.1 -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Expected results
So, I have a table with following userWeight and have to define a metric called Reach(%). Now Reach for a service is defined as
SUM(userWeights for the service)/SUM(All userWeights for Distinct Users)
The workaround I found out was SUM(DISTINCT userWeights)/(SELECT SUM(DISTINCT userWeights) FROM Table_Name)
Now this works great at the Overall Level, But I have a Filter Box for Age Group, Gender, and City. So, Ideally, I would want
SUM(DISTINCT userWeights) / (SELECT SUM(DISTINCT userWeights) FROM Table_Name WHERE [Conditions From Filter Box])
Actual results
What is happening is SELECT service,SUM(DISTINCT userWeights) / (SELECT SUM(DISTINCT userWeights) FROM Table_name) WHERE gender = "Male" and age_group = "24 - 30 yrs"
Which Produces Wrong results for the required Metric. I know I can do it Via SQL Lab from Jinja Templating. But I don't know how to do it
I am less aware of Jinja templating and particularly how do we do it for Superset.
I am getting following stacktrace while running a query
Sorry, something went wrong
500 - Internal Server Error
Stacktrace
Please help me because its kinda a SOS situation.
Steps to reproduce