Count nulls in script

[jessica_webb]

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?

[tresesco · Accepted solution]

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.

[Anil_Babu_Samineni]

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

[PrashantSangle]

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

[jessica_webb]

Exactly the problem! Thank you so much

[effinty2112]

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]

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]

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