IsNull, Count and Group by?

[#1]

Hi all,

I am trying to do the following:

LOAD
 Value1,
 Count(distinct Timestamp1) AS CountX
Resident Data123
Group by Value1;

Timestamp1 sometimes does not exist ('-') and I'd like to have a formula that replace the '-' by a 0.

I was thinking about using an if and IsNull expression, but I always get an "invalid expression" message while running the script. Maybe this is because of the the "Group by"-clause?

I have already tried:

if(IsNull(Timestamp1), 0, If(Timestamp1>0, Count (distinct Timestamp1), 0))

and as second solution I used the first code above and reload the Data into a new table with the same data plus this:

If (IsNull(CountX), '0', CountX) as CountXfinal

Both solutions do not brought the correct answer yet. In the Layout I still have no results with a 0 but still with '-'.

Maybe there is just a mistake in the code I don't see... Any suggestion?

[#2 · Accepted solution]

GROUP BY is expecting aggregation, so count must be first:

COUNT(DISTINCT IF( ISNULL(Timestamp1), 0, Timestamp1) )