Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

agg. count should return 0

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

1 Solution

Accepted Solutions
Not applicable
Author

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;


View solution in original post

5 Replies
Not applicable
Author

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;


Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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?

Not applicable
Author

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. Smile You can do this.