topic Count query in QlikView

Count query

Wed, 29 Dec 2010 15:08:17 GMT

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

Count query

Wed, 29 Dec 2010 15:25:32 GMT

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

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

Rgds,

Sébastien

Count query

Wed, 29 Dec 2010 15:59:02 GMT

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

Count query

Wed, 29 Dec 2010 16:04:44 GMT

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

Count query

syed_muzammil — Wed, 29 Dec 2010 16:09:18 GMT

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)

Count query

Wed, 29 Dec 2010 16:46:29 GMT

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

Count query

Wed, 29 Dec 2010 17:09:35 GMT

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

Count query

Thu, 30 Dec 2010 16:45:04 GMT

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!

Count query

Thu, 30 Dec 2010 16:53:09 GMT

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 😉

Count query

Miguel_Angel_Baeyens — Thu, 30 Dec 2010 16:59:34 GMT

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.

Count query

Miguel_Angel_Baeyens — Thu, 30 Dec 2010 17:05:27 GMT


cayfmatt wrote: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!

Agree. Fortunately, there are some good settings to check in QlikView. Go to the Settings menu, User Preferences, and tab "Save". Click on "Save before reload" and set the others as you wish. All these settings are documented in the Reference Manual available in the Dowloads section above as well as when you install QlikView choosing "Complete Install".

Hope this helps!

Count query

Fri, 31 Dec 2010 13:37:04 GMT

Thanks Sebastian and Miguel.

Miguel - I have been getting the employstatus from another section of coding within Edit Script but didnt want to paste it all in. I'm now down to about 22 'wrong' records from over 200 before so thanks again, I'll continue playing around with it (also now enabled the auto save function )