How can I create a metric that is going to filter empty values like the one below, by if an empty value doesn’t yet exist? In other words how can I make the filter so it filters out future empty values?
select Customer Gender where Customer Gender not in ((empty value))
Best answer by Jan KosView original
I'm not sure if i quite understand your question. You could use IFNULL statement to replace an empty value with actual 0 and pair it with > 0 filtering in a metric. And it will be filtering even if new data is added. Please note that metrics are producing number and need to be used with some aggregation function (SUM, AVG, COUNT, etc.)
OK sorry, I don’t think I did a good job explaining my question before.
So this is the actual metric:
SELECT COUNT(Conversation) WHERE Customer Gender [Customer Gender] IN ((empty value))
Now this is reality looks like this:
SELECT COUNT([/gdc/md/qplm123456n2d919mlflrxdgi6gs1qv/obj/1582]) WHERE [/gdc/md/qplmcrc9y1234569mlflrxdgi6gs1qv/obj/1747] IN ([/gdc/md/qplmcrc9123456mlflrxdgi6gs1qv/obj/1746/elements?id=1478])
So the problem with this (I think) is that the query asssumes that the empty value exists in order to find the uri with the element id.
How can I replicate a metric like this assuming that an empty value doesn’t yet exist, how can I apply the IFNULL statement here?
There shouldn't be an issue with your metric. Empty value has its own object identifier and any additional empty values loaded will automatically be located under this object identifier, therefore it'll filter out future empty values - considering empty values are always uniform in your source data. For example:
In the above table a have basically two kind of empty values. The “blank field” and “NULL”. Both of these are “empty values” in GoodData but both would have its own identifier and would show as separate “empty value” in the metric editor
It is a best practise to stick to one type of NULL value in a source data.