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?
Solved! Go to Solution.
You said, You are using this in text box? Can you try that by help Group By during aggregation involved, May be?
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.
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.
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.