topic agg. count should return 0 in QlikView

agg. count should return 0

Tue, 17 Aug 2010 13:38:07 GMT

<body>I have a problem with count(*). If there is no count, no value is returned. In the below example i would have expected the following output in table OUT: antal, TEST_PID, dato 0, x.y.z, 2101 Instead i get no output at all 😕 Very strange as this is working in MSSQL TEMP: LOAD * INLINE [KOL1, KOL2 <st1:metricconverter productid="1, a" w:st="on">1, a</st1:metricconverter> 2, b 3, c 4, d]; // OUT: LOAD count(*) as antal, 'x.y.z' as TEST_PID, '2101' as dato RESIDENT TEMP WHERE KOL1<0; drop table TEMP; </body>

agg. count should return 0

Tue, 17 Aug 2010 14:28:10 GMT

Try this:


TEMP:
LOAD *
INLINE [KOL1, KOL2
 1, a
 2, b
 3, c
 4, d];
//
OUT:
LOAD sum(if(KOL1<0, 1, 0)) as antal,
 'x.y.z' as TEST_PID,
 '2101' as dato
RESIDENT TEMP;

drop table TEMP;

agg. count should return 0

Tue, 17 Aug 2010 14:33:31 GMT

Hi,

Thanks for quick reply. Hmm, probably should work, however it is a work-around. Why is it that count(*) doesn't return the value 0. Qlikview works very different in this matter compared to MSSQL.

I have just rewritten my true query:

LOAD count(*) as KPI_VALUE,
date($(RELOAD_DTM)) as TIME_DAY,
'GENEVA_ERR: 30 days' as DESCRIPTION,
'$(PID_GEN_ERROR_30)' as PID
RESIDENT GENEVA_ACCOUNTS
WHERE (DATE_NEXT_BILL_DATE<today()-30 AND not BILLING_STATUS='OK');

to this one:

LOAD sum(IF(DATE_NEXT_BILL_DATE<30 AND not BILLING_STATUS='OK',1,0) as KPI_VALUE,
date($(RELOAD_DTM)) as TIME_DAY,
'GENEVA_ERR: 30 days' as DESCRIPTION,
'$(PID_GEN_ERROR_30)' as PID
RESIDENT GENEVA_ACCOUNTS;

Best regards

Mikkel

agg. count should return 0

Tue, 17 Aug 2010 14:49:52 GMT

QV does not support SQL syntax for Load statement. When you'd restricted the recordset by your condition KOL1<0 the result is - NO RECORDS. It means there is NO RECORDS for any operations.

agg. count should return 0

Tue, 17 Aug 2010 15:26:18 GMT

I would expect count(*) to output 0 in the result set from the query.

Instead it produces nothing, so why don't you think this is a bug?

agg. count should return 0

Tue, 17 Aug 2010 16:28:17 GMT

My expectations are the same for the result. Maybe you are right, it's a bug. But maybe it's a feature. Anyway we won't know it for sure till we send a ticket to a support team. You can do this.