Hi! I need to make an MAQL query in order to divid...
# gooddata-cn
Hi! I need to make an MAQL query in order to divide COUNT of jobpostingid attribute WHERE applicationstatus = Hired by total COUNT of jobpostingid attribute. I tried to send this query: "maql": "SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) WHERE {attribute/attr.dshb_recruiting_ratio.applicationstatus} = Hired/ COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid})" but got message: "The MAQL query='PARSE METRIC SELECT COUNT({attribute/890c63a7e00143009206e44e1f8fdd29:attr.dshb_recruiting_ratio.jobpostingid}) WHERE {attribute/890c63a7e00143009206e44e1f8fdd29:attr.dshb_recruiting_ratio.applicationstatus} = Hired/ COUNT({attribute/890c63a7e00143009206e44e1f8fdd29:attr.dshb_recruiting_ratio.jobpostingid})' could not be parsed.", How should I make the query in a proper way?
Hi! I assume attr.dshb_recruiting_ratio.jobpostingid is identifier of the jobpostingid attribute. You have following options: Use multiple metrics Hired:
Copy code
SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) WHERE {attribute/attr.dshb_recruiting_ratio.applicationstatus} = "Hired"
(do not forget to quote strings in GD.CN) All:
Copy code
SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid})
Copy code
SELECT {metric/Hired}/{metric/All}
Use brackets for nesting of metric expressions Note that WHERE clause comes after the select clause in which you specify aggregation function, arithmetics formulas etc. You can however nest other metric definition using brackets if you cannot reference existing metric with desired metric definition.
Copy code
 ( SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) 
   WHERE {attribute/attr.dshb_recruiting_ratio.applicationstatus} = "Hired"
 ) / COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid})
this will let you specify WHERE clause for the count in numerator of the fraction but not in the denominator.
I tried
Copy code
"maql": "SELECT(SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) WHERE {attribute/attr.dshb_recruiting_ratio.applicationstatus} = 'Hired')/COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid})"
Copy code
"The MAQL query='PARSE METRIC SELECT(SELECT COUNT({attribute/890c63a7e00143009206e44e1f8fdd29:attr.dshb_recruiting_ratio.jobpostingid}) WHERE {attribute/890c63a7e00143009206e44e1f8fdd29:attr.dshb_recruiting_ratio.applicationstatus} = 'Hired')/COUNT({attribute/890c63a7e00143009206e44e1f8fdd29:attr.dshb_recruiting_ratio.jobpostingid})' could not be parsed."
You have to really use double quotes (instead of single quotes) for filter strings:
Copy code
"SELECT(SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) WHERE {attribute/attr.dshb_recruiting_ratio.applicationstatus} = \"Hired\")/COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid})"
And what is wrong with the query: "SELECT  COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) USING {attribute/dshb_recruiting_ratio.id} WHERE {attribute/attr.dshb_recruiting_ratio.applicationstatus} = \"Hired\""
You have to specify USING after WHERE.
I see that we miss info about SELECT keywords order in our documentation. We will update that.
specified USING after WHERE "SELECT(SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) WHERE {attribute/attr.dshb_recruiting_ratio.applicationstatus} = \"Hired\" USING {attribute/dshb_recruiting_ratio.id})/COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid} USING {attribute/dshb_recruiting_ratio.id})" - could not be parsed.
"SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) WHERE {attribute/attr.dshb_recruiting_ratio.applicationstatus} = \"Hired\" USING {attribute/dshb_recruiting_ratio.id}" could not be parsed - why don't you use DISTINCT notation?
Yes, but once you are using metric more complicated then simple aggregation you have to always enclose it in a subselect: "SELECT(SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) WHERE {attribute/attr.dshb_recruiting_ratio.applicationstatus} = \"Hired\" USING {attribute/dshb_recruiting_ratio.id})/ (SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) USING {attribute/dshb_recruiting_ratio.id} )"
why don't you use DISTINCT notation
MAQ COUNT has DISTINCT semantics by default
Btw did you consider using UI measure editor? (accesible under
) That should help you with most of the syntax issues.
I added your query using UI measure editor "SELECT(SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) WHERE {attribute/attr.dshb_recruiting_ratio.applicationstatus} = \"Hired\" USING {attribute/dshb_recruiting_ratio.id})/ (SELECT COUNT({attribute/attr.dshb_recruiting_ratio.jobpostingid}) USING {attribute/dshb_recruiting_ratio.id} )" but it returnes no data
The table itself contains data
I managed to save this measure, but it shows no data when added to an insight
The measure without WHERE condition shows data
The message "sorry, we can't display this insight" means that there's something wrong with the configuration of your insight, not that it returns no data. Most probably the attribute in the WHERE condition is unrelated to the other attributes/facts in your insight.
I see now that you are using attribute in the filter whereas you should be using label instead.
@Василий Островский Did you eventually manage to create the metric/insight according to your needs?
Yes. Thank you David! When will be metrics editor button available in CN UI?
Hi, we plan to release Metric editor officially with version 1.4, that is scheduled to the end of September/beginning of October.