Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to create a set analysis and exclude null records on a field. Something like this:
Sum({$<Group -= Null()>} Amount)
I found something saying to do it like the following line, but the editor still gives me a curly red underlining.
Sum({$<Group={*}>} Amount)
Could someone give me the right way to do this?
I'm working with QV version 11 RC.
I think I know the right syntax.
If you want to choose only NULL values you should use:
Sum( {$ <Group = {$(=list_of_groups_with_NULL)}> } Amount )
where list_of_groups_with_NULL is generated like this:
Concat( DISTINCT TOTAL If(IsNull(Group), Chr(39) & Group & Chr(39)), ', ')
Now you can just use this statement with "-" to choose all groups without NULLs:
Sum( {$ <Group - = {$(=list_of_groups_with_NULL)}> } Amount )
No, Some time it used to say the expression is error but still it work fine.
Just go ahead and check the result for the expressions.
Regards,
Srini.
Shall I report this as a bug?
Hi,
try this expression.
Sum({$<Group -= {""}>}Amount)
Regards,
Kaushik Solanki
Hi Kaushik,
QV still gives a syntax error for your expression. As Srini mentioned the code is working, but the syntax error is annoying.
I think this is a known bug of the syntax checker. If you don't like the misleading false syntax error, use something like
Sum({$<Group = Group - {Value}>}Amount)
It's kind of Bug.
Since they didn't mention that as a correct syntax.
You can just inform to Qlikview Support.
Regards,
Srini.
Hi,
I would use somthing like this...
Sum({$<Group -= "=Null()">} Amount)
Cheers - DV
There's no way to select nulls or unselect nulls, this is what you should do, select everything else
Sum({$<Group = {'*'}>} Amount)
Daniel - Not sure...but my expression works and it excludes nulls. I have used the above expression many times with QV SR7.
Thanks -DV