Count query

[#1]

Hi guys,
I hope someone can help.

I have a seemingly basic query where I wish to count the number of records from our ODBC database where field name pova = 0 , where fields crbrenh,crbrbasic & crbrenh are NULL and where employstatus <> 999001302

I have tried various different coding but it does not return what I know to be correct. I have put the fields into a table to see what I am looking at and the fields crbrenh,crbrbasic & crbrenh appear to be a mixture of blanks and - so I suspect this is part or all of the problem.

Any ideas?

Thanks,

Matt

[#2]

sum({$<pova={0},crbrenh=,crbrbasic=,employstatus-={999001302}>} 1)

I didn't tested it but should be something like that.

Rgds,

Sébastien

[#3]

Thanks for your reply Sebastian however this is not working and I have tried different variations on this as well.

sum({$<pova={0},crbrenh=,crbrbasic=,employstatus-={999001302}>} 1)

As I wish to exclude the value 999001302, I presume you mean the following instead?

sum({$<pova={0},crbrenh=,crbrbasic=,employstatus<>{999001302}>} 1)

Also is there any significance in the crbrenh=,crbrbasic=, code as I have previously thought it should be crbrenh=0 or isnull(crbenh) if I want to count blank values?

Thanks,

Matt

[#4]

I'm not sure about the fact to do a sum of 1...

The Set analysis explanation is:

"crbenh=" : That means take all values for this field. So it will exclude the null values

"employstatus -={999001302}" there is a "-" before the "=": That means take everything except 999001302.

You should try to replace the Sum( by count if you ha ve an ID field to count on :

count(distinct {$<pova={0} .....>} IFFIELD) ...

If still not working, test the count by adding each condition one by one...

Rgds,

Sébastien

[syed_muzammil]

Hi,

Better add some fields at the script level

if(len(trim(crbrenh))=0,1,0) as crbrenhFlag,

if(len(trim(crbrbasic))=0,1,0) as crbrbasicFlag,

And change your expression to---

Count({$<pova={0},crbrenhFlag={0},crbrbasicFlag={0},employstatus={'<>999001302'}>} the field you want to count)

[#6]

Thanks Syed,

I have tried pasting your if commands in the Edit Script function after the rest of the existing commands as follows:

SQL SELECT empid as personno, crbrbasic, crbrstand, crbrenh, pova, payrollno
FROM XXX.dbo.employee where current_ = 1 and payrollno <> ' ';

if(len(trim(crbrenh))=0,1,0) as crbrenhFlag;
if(len(trim(crbrstand))=0,1,0) as crbrstandFlag;
if(len(trim(crbrbasic))=0,1,0) as crbrbasicFlag;

but get the following message (which I have only got since I pasted your coding)

Error in expression:
')' expected

I cannot see where a ')' should fit in this? Should this be elsewhere in the Edit Script command?

Thanks,

Matt

[#7]

Your script should be like that:

LOAD *,

if(isnull(crbrenh),1,0) as crbrenhFlag,

if(isnull(crbrstand),1,0) as crbrstandFlag,

if(isnull(crbrbasic),1,0) as crbrbasicFlag;

SQL SELECT empid as personno, crbrbasic, crbrstand, crbrenh, pova, payrollno
FROM XXX.dbo.employee where current_ = 1 and payrollno <> ' ';

if(len(trim(crbrenh))=0,1,0) as crbrenhFlag;
if(len(trim(crbrstand))=0,1,0) as crbrstandFlag;
if(len(trim(crbrbasic))=0,1,0) as crbrbasicFlag;

Then try the expression as advised...

But the -= works fine, there is no need to do {'<>999xxxxx'}...

But if you prefer like that...

Rgds,

Sébastien

[#8]

Thanks Spastor but I have tried your suggestion and it is still coming back with the same error Error in expression:
')' expected:

LOAD *,

(if(isnull(crbrenh),1,0) as crbrenhFlag),

(if(isnull(crbrstand),1,0) as crbrstandFlag),

(if(isnull(crbrbasic),1,0) as crbrbasicFlag);

SQL SELECT empid as personno, crbrbasic, crbrstand, crbrenh, pova, payrollno
FROM xxx.dbo.employee where current_ = 1 and payrollno <> ' ';

if(len(trim(crbrenh))=0,1,0) as crbrenhFlag;
if(len(trim(crbrstand))=0,1,0) as crbrstandFlag;
if(len(trim(crbrbasic))=0,1,0) as crbrbasicFlag;

I have to say, as a complete beginner, Qlikview can be extremely frustrating (especially when you do not save before Edit Script and if it doesnt work & comes back with an error, you lose all of your changes!

[#9]

I think the goog pne is :

LOAD *,

if(isnull(crbrenh),1,0) as crbrenhFlag,

if(isnull(crbrstand),1,0) as crbrstandFlag,

if(isnull(crbrbasic),1,0) as crbrbasicFlag;

SQL SELECT empid as personno, crbrbasic, crbrstand, crbrenh, pova, payrollno
FROM xxx.dbo.employee where current_ = 1 and payrollno <> ' ';

Don't forget to save before reloading 😉

[Miguel_Angel_Baeyens]

Hello Matt,

The script, as suggested by Syed adn Sébastien, should look like

Table:LOAD *, // loads all previously fields pulled from the database in the SQL SELECT query If(Len(crbrenh), 0, 1) as crbrenhNullFlag, // If length of field is zero (null or empty or missing) from the database, then store a "1" in this field, otherwise "0" If(Len(crbrstand), 0, 1) as crbrstandNullFlag, If(Len(crbrbasic), 0, 1) as crbrbasicNullFlag; // Missing employstatus below!SQL SELECT empid as personno, crbrbasic, crbrstand, crbrenh, pova, payrollnoFROM xxx.dbo.employee where current_ = 1 and payrollno <> ' ';

I always use Len(field) instead of IsNull() because some strange behaviour in different 32bits and 64 bits architectures. Len(Field) will always be equal to zero when there is no value stored in that field in the record.

Now create a new text object and type the following

Count({< pova = {0}, crbrenhNullFlag = {1}, crbrstandNullFlag = {1}, crbrbasicNullFlag = {1}, employstatus -= {999001302} >} personno)

Note that in QlikView field names and values are case sensitive. Note that field "employstatus" used in the expression above is not being loaded by your script nor retrieved from the database in your SQL statement, so you should add it.

Hope that helps.