topic Or in SetAnalysis in QlikView

Or in SetAnalysis

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

Hi all,

I want to use or in set analysis how can I write the syntax?

I want to write belongs to = null or empty how can I write for empty?

count({<belongs={'NULL'} >}Number)

Thanks

Sony

Re: Or in SetAnalysis

dberkesacn — Wed, 12 Jul 2017 09:50:17 GMT

Hi,

The"+"-sign is used for OR in set analysis

Daniel

Re: Or in SetAnalysis

tresB — Wed, 12 Jul 2017 09:50:48 GMT

This?

count({<belongs={'NULL', 'empty'} >}Number)

Re: Or in SetAnalysis

soniasweety — Wed, 12 Jul 2017 09:52:23 GMT

I have no value as empty its blank like empty space.can I use this?

count({<belongs={'NULL', ' '} >}Number)

Re: Or in SetAnalysis

antoniotiman — Wed, 12 Jul 2017 09:53:28 GMT

Hi,

try this

Count({<belongs={"=Len(Trim(belongs))=0"}>} Number)

Regards,

Antonio

Re: Or in SetAnalysis

dberkesacn — Wed, 12 Jul 2017 09:54:12 GMT

with this yes

NullAsValue ‒ QlikView

Re: Or in SetAnalysis

tresB — Wed, 12 Jul 2017 09:54:24 GMT

If you are sure that it's a space you can try like that. Otherwise, null, blanks, space, nothings are tricky in data. you have to carefully deal with them.

Re: Or in SetAnalysis

arvind_patil — Wed, 12 Jul 2017 09:59:24 GMT

Hi Sony,

I am Agree with Daniel.Use + Sign for Or Condition Like below.

Sum({<Type={"A"}>+<Type={"z"}>}Sales)

so in above expression, it will show Sale where Type equals A or Z.

Thanks,

Arvind Patil

Re: Or in SetAnalysis

soniasweety — Wed, 12 Jul 2017 10:00:55 GMT

yes your expression is only for null values ,its working,

I have in data NULL also.

sample:

LOAD * Inline [

number,belongs,data
1,aa,123
2,bb,22
3,cc,34
4,NULL,54
5,NULL,121
6, ,127
7,NULL,88
];

Re: Or in SetAnalysis

soniasweety — Wed, 12 Jul 2017 10:01:37 GMT

Am unable to mark Helpful the replies ?

Re: Or in SetAnalysis

soniasweety — Wed, 12 Jul 2017 10:02:10 GMT

Nice example thanks

Re: Or in SetAnalysis

Kushal_Chawda — Wed, 12 Jul 2017 10:03:26 GMT

better way to do it is to create the flag in script and use that in set analysis

LOAD ...

if(len(trim(belongs))<=0 or lower(trim(belongs))='null' , 1) as EmtyFlag

Now use set analysis like below

Count({<EmtyFlag={1}>}Number)

Re: Or in SetAnalysis

soniasweety — Wed, 12 Jul 2017 10:07:33 GMT

thanks kush. instead of count of ,sum of numbers I want to show that numbers along with it .

can I use the Only function instead of count/sum? is it make sense?

Re: Or in SetAnalysis

dberkesacn — Wed, 12 Jul 2017 10:10:13 GMT

I think no because only gives you the value if the field has only one and if the field has not only one value returns null()

Re: Or in SetAnalysis

dberkesacn — Wed, 12 Jul 2017 10:11:22 GMT

great doc, i suggest check the picture.

Re: Or in SetAnalysis

soniasweety — Wed, 12 Jul 2017 10:15:41 GMT

thanks for the info, if I want to show the numbers how can I achieve this?

Re: Or in SetAnalysis

soniasweety — Wed, 12 Jul 2017 10:15:57 GMT

attachment missed I think sir ..

Re: Or in SetAnalysis

dberkesacn — Wed, 12 Jul 2017 11:16:00 GMT

indeed: here you are How to use - Only()

the blog has a nice simple picture plus usefull desc..