topic Re: Count nulls in script in QlikView

Count nulls in script

jessica_webb — Wed, 25 Nov 2020 16:16:04 GMT

I think this should be very straightforward, but after reading several forum responses, I still can't find a working solution.

I have a large dataset. If a particular field (Target) is null in all cases, then I would like to use certain expressions in my qvw. However, if the field as some values (i.e. not all null), then I would like to use different expressions.

My solution to this was to create a new field in the script, that would flag which route to go down. I used the following expression (amongst many other attempts):

IF(COUNT(IF(LEN(TRIM(Target))>0,Target))>0,'Combined','All') as Report_type

Using this expression in a text box on the qvw seems to work, and I get the appropriate 'Combined' or 'All' depending on which dataset I load. But in the script, I get an 'invalid expression' warning.

Why isn't it working in the script?

Re: Count nulls in script

tresB — Wed, 19 Jul 2017 09:20:09 GMT

In the script if you use aggregation function(like count(), sum(), ...), you have to use Group By clause as well. Perhaps, you are missing that.

Re: Count nulls in script

Anil_Babu_Samineni — Wed, 19 Jul 2017 09:21:50 GMT

You said, You are using this in text box? Can you try that by help Group By during aggregation involved, May be?

Re: Count nulls in script

PrashantSangle — Wed, 19 Jul 2017 09:22:34 GMT

Because of aggregation function which your using.

Aggregation function required group by clause.

You can also create flag in script like

if(len(trim(Target))=0,'null','Not Null') as flag

and then use flag in front end.

Regards

Re: Count nulls in script

jessica_webb — Wed, 19 Jul 2017 09:23:28 GMT

Exactly the problem! Thank you so much

Re: Count nulls in script

effinty2112 — Wed, 19 Jul 2017 09:40:11 GMT

Hi Jessica,

I know I'm coming late to this party but you could consider

FieldValueCount(Target) in your script.

I can't remember if it returns zero or is null if the Target field has no values but either way it might be an easy test.

Cheers

Andrew

Re: Count nulls in script

jessica_webb — Wed, 19 Jul 2017 09:46:14 GMT

Hi Andrew,

Thanks for the suggestion. When using that in a textbox it gives me a null value... Not sure what its function is?

Thanks,
Jess

Re: Count nulls in script

effinty2112 — Wed, 19 Jul 2017 09:55:47 GMT

Hi Jessica,

FieldValueCount returns the number of unique values of a field and can be used in the UI or in script. The neat thing about it is that it doesn't do this by trawling through all the data. It gives the number of rows in a field's symbol table.

To explain what that's all about there is no-one better than hic.

Symbol Tables and Bit-Stuffed Pointers