topic Re: Help with set analysis in QlikView

Help with set analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I need help with the following expression -

Count({$<field={'NULL'}>}field)

However, I want it to do the opposite of this. I want it to ignore the 'NULL'. I tried replacing the '=' with '<>' but that didn't work.

Any suggestions?

Thanks

Greg

Re: Help with set analysis

beck_bakytbek — Thu, 23 Mar 2017 14:13:52 GMT

Hi Greg,

you can make in script the flag, and with flags exclude the zero values within your charts

beck

Re: Help with set analysis

Anonymous — Thu, 23 Mar 2017 14:15:24 GMT

I don't understand

Re: Help with set analysis

Anil_Babu_Samineni — Thu, 23 Mar 2017 14:16:18 GMT

Then use this, BEfore = there is one -(Hyphan)

Count({$<field -={'NULL'}>}field) // I am assuming you have NULL name of value in that field

Re: Help with set analysis

annafuksa1 — Thu, 23 Mar 2017 14:18:41 GMT

try

Count({$<field=-{'NULL'}>}field)

Re: Help with set analysis

Anonymous — Thu, 23 Mar 2017 14:31:56 GMT

Thanks. That seems to work but the expression looks like it has errors even though it says 'Expression OK' -

Re: Help with set analysis

Peter_Cammaert — Thu, 23 Mar 2017 14:36:02 GMT

Just ignore the wiggly lines underneath your expression. The syntax checker doesn't know about negation nor does it know about compound operators (e.g. -=). Indeed "Expression OK" says it all...

Re: Help with set analysis

annafuksa1 — Thu, 23 Mar 2017 14:36:28 GMT

its a bug in Qlikview

it is working perfectly fine (I use is in many calculations:))

you can use as well

Count(if(field<>'NULL',field)

Re: Help with set analysis

annafuksa1 — Thu, 23 Mar 2017 14:38:03 GMT

but use Count({$<field=-{'NULL'}>}field) not Count({$<field-={'NULL'}>}field) as they are working if different way (I will strongly recommend to test it and see )

Re: Help with set analysis

Peter_Cammaert — Thu, 23 Mar 2017 14:39:25 GMT

A small warning: taking everything except NULL in this way, will include all InitialSDSStatus values not currently selected. If you want to exclude NULL from the currently selected values, use

Count({$<InitialSDSStatus -= {'NULL'}>} InitialSDSStatus)

Re: Help with set analysis

sasiparupudi1 — Thu, 23 Mar 2017 14:40:31 GMT

or may be this

Count({$<field ={'*'}-{'NULL'}>}field)

Re: Help with set analysis

Peter_Cammaert — Thu, 23 Mar 2017 14:44:47 GMT

I just reread your initial post. You know why = works in a set modifier, but <> doesn't? That's because the equal sign means 'temporarily assign the set of values on the right to the field on the left'. It's not a comparison operator, it's a (temporary) assignment that lasts for the duration of the expression calculation.

Just like the following is a valid script assignment

LET vField = 'ABC' & chr(10) & 'DEF';

but this one is not:

LET vField <> 'GHI';

Re: Help with set analysis

beck_bakytbek — Thu, 23 Mar 2017 14:46:19 GMT

Hi Greg,

look at this: Flags in script

i hope that helps