Using IFNULL + IF THEN ELSE to fill gaps in the visualisation of client data

tomas.ferko@keboola.com

Use case:

Client XY wants to visualise a trend using a line or area chart. The problem is however that there are missing entries for some days. The result is that we are unable to display a coherent line in the requested chart, instead what we get are isolated data points. The simplest solution would be to wrap the metric in a simple IFNULL clause and substitute the empty values for “0” or other number.

However, this does not bring feasible results in cases when we're trying to visualise a trend of (for example) the ranking of a website that is being checked once a week. That means if the ranking is checked on monday and is “5” does not mean that it's “0” on tuesday.

This is where the usage of IFNULL + IF THEN ELSE comes into play.

----------------------------------------------------------------------------------------------------------------------------

From MAQL reference guide:

IF THEN ELSE statements return one of two possible values, or perform one of two possible computations, depending on whether some condition is met. The defined condition follows the keyword IF and can be constructed using any of the filtering keywords or relational operators (IN, NOT IN, BETWEEN, NOT BETWEEN, =, < , <=, >, >=, <>).

The first possible outcome follows the keyword THEN, and the second possible outcome follows the keyword ELSE. The outcomes that are returned can be numerical values or arithmetic operations. All IF THEN ELSE statements conclude with the END keyword.

Syntax

SELECT IF … THEN … ELSE … END

- SELECT IF condition THEN number ELSE number END

- SELECT IF condition THEN arithmetic_operation ELSE arithmetic_operation END

Examples

- SELECT IF SUM(Amount)>= AVG(Amount) THEN 10 ELSE 0 END

- SELECT IF SUM(Duration) - AVG(Duration) > 2000  THEN 0 ELSE 1 END

- SELECT IF AVG(Probability) > 0.5 THEN SUM(Amount) * 10 ELSE SUM(Amount) / 10 END

----------------------------------------------------------------------------------------------------------------------------

Howto:

Let's assume all we need is a simple sum wrapped in an IFNULL clause for the base metric.

Let's call it [SUM]Amount

SELECT IFNULL(SUM(Amount),0)

Now we'll wrap it into another metric that will basically tell GoodData that if the outcome of the previous metric is 0 it should return the average value of that metric for the past week.

SELECT IF [SUM]Amount = 0

THEN (SELECT AVG([SUM]Amount) BY Week/Year)

ELSE [SUM]Amount

END

----------------------------------------------------------------------------------------------------------------------------

Desired Outcome Example: