If I use use as example datasets products with primary key product_id and orders with primary key order_id and foreign key product_id, is there a way to create a pivot table that contains every product that is not in an order?

Hi Daniel, You can use the IFNULL statement. This can allow you to display missing data in your tables. A simple metric using your example datasets/attributes would be something like this:

SELECT IFNULL (COUNT (order_id), 0) + 0

And then you can add it in the Analytic Designer, slicing it by the Product_ID. This will display all products and the count of their order IDs; when this count is Null, it will display a 0. The next step would be to filter out values that aren’t 0. You can easily do that on the Insight itself, by adding a filter for the metric and setting it to only show when the metric = 0 (see my screenshot). I’d also recommend taking a look at the community article https://community.gooddata.com/data-models-kb-articles-46/how-to-display-null-or-missing-values-98 for some additional insight.

Just out of curiosity, what is the point of the

+0

at the end?

It’s meant to “force” a LEFT OUTER JOIN, ensuring the zeroes are shown. It’s a bit of a workaround, because in some more complex calculations the GoodData engine (by design) does not display the nulls/zeroes, even when using the IFNULL statement. I’ve seen the same being achieved by adding a

*1

at the end, too. Some IFNULL metrics will work without it, but it’s good practice to add the

+0

anyway, to ensure it works.