IsNull, Count and Group by?

Tue, 15 Dec 2009 21:33:13 GMT

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?

IsNull, Count and Group by?

Wed, 16 Dec 2009 01:32:00 GMT

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

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

topic IsNull, Count and Group by? in QlikView

IsNull, Count and Group by?

IsNull, Count and Group by?