Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
jessica_webb
Contributor III

Count nulls in script

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?

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Count nulls in script

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.

7 Replies
MVP
MVP

Re: Count nulls in script

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

Re: Count nulls in script

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

Great dreamer's dreams never fulfilled, they are always transcended.
jessica_webb
Contributor III

Re: Count nulls in script

Exactly the problem! Thank you so much

effinty2112
Honored Contributor

Re: Count nulls in script

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

jessica_webb
Contributor III

Re: Count nulls in script

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

effinty2112
Honored Contributor

Re: Count nulls in script

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