We have been working on setting up Achilles for the first time and there are a few of the treemap graphs that don’t display. Tracing it back I believe it is due to the prevalence for each row (Conditions, Condition Era, Drug Exposures) being calculated at 0%. We have other treemaps based on prevalence that do get calculated and displayed correctly.
Digging into the sql itself (e.g. sqlConditionTreemap.sql) I can run the sql directly and it brings back the correct results and prevalence calculations but the JSON files appear to incorrect prevalence calculations from the results of the SQL statements.
Interestingly the records per person calculation doesn’t display as I would expect as it has been rounded to the nearest whole number.
Has anyone seen this before?
Hi, Matt. It has been a while since I ran R code directly. but one guess is that it’s a translation issue between sql result to R dataframe, or R dataframe to JSON output. If you could execute a single query in the context of an R dataframe and view those results, that might give us an idea of where the problem is coming from. But the issue you are experiencing does look like it’s a truncation issue.
-Chris
1 Like
Hi Chris,
We are also facing same issue. and we find out that the issue is with SQLRENDER. those Treemap sql which uses a round function with expression like “round(1.0*ar1.count_value / denom.count_value,5) as percent_persons” is translated as "ROUND(CAST(1.0*ar1.count_value / denom.count_value AS NUMERIC),5) as percent_persons"
Thing to notice here is CAST to NUMERIC is not specified with any precision and scale value . And according to Redshift documentation here http://docs.aws.amazon.com/redshift/latest/dg/r_Numeric_types201.html when NUMERIC scale value is not specified it is by default set to zero.
This may require some change in either ACHILESE R package or in SQLRender package. Please suggest your thoughts on this.
1 Like
@schuemie mentioned that he made an update for the mappiings in sqlRender. If you could ensure you have the latest package and try again, and let us know if your results improve.
Thanks for the help and the update in the sqlRender. Our team was able to deploy it and rerun the modules and it fixed the prevalence calculations.