Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator 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?

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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.

View solution in original post

7 Replies
Highlighted
MVP
MVP

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.

View solution in original post

Highlighted

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)
Highlighted

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.
Highlighted
Creator III
Creator III

Exactly the problem! Thank you so much

Highlighted
Master
Master

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

Highlighted
Creator III
Creator III

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

Highlighted
Master
Master

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