hi, I’m trying to understand behaviour on a visual...
# gooddata-cloud
hi, I’m trying to understand behaviour on a visual. I have a list of all customers in one table and a list of transactional data on another, linked by a key. So far, so simple. When I want to get a count of transactions per service, the visual doesn’t show services with 0 values, which seems odd. Should I be able to achieve this in a table?
structure is
so the service_id is the key and I want to do effectively a
Copy code
SELECT 'Service id lookup'.'Service name',
        COUNT('Child notes'.'service ID (count)') as activity_count
FROM 'Child Notes'
GROUP BY 'Service id lookup'.'Service name'
This appears to work except it doesn’t show 0 values
Hi Jamie, you can handle null values at MAQL level, the language used to create metrics in GoodData Cloud. The keyword that you have to use is IFNULL, however, note that this will only allow you to display null values and replace them with a string, commonly a “0”, as long as there is a placeholder for the missing data in your database, if there is no “hint” of the value missing in your database table, then GoodData will not be able to force the 0 to appear. However, this is not a limitation when using FOR EACH, it acts as a SQL full outer join and the missing data will “appear” even if they are truly missing.
You may also check “show missing values” in Analytical Designer for the Service ID attribute in your insight. The Service IDs without transactions will appear in results with NULL values. You can either format null as zero using format
if the value is displayed in table (or use other text which describes the situation) or use IFNULL function as suggested by Moises above and replace NULL value by real 0 value. The metric format should be sufficient for tables but you may need to use IFNULL function in visualizations. The metric with FOR EACH will always fill missing values even if “show missing values” is not checked.
thanks for the response will look into this now
seems it was as simple as
🙌 1