topic Re: 2 fields not equal to 0 in set analysis in QlikView

2 fields not equal to 0 in set analysis

Anonymous — Mon, 27 Apr 2015 20:53:16 GMT

How do i write the following statement in set analysis?

[College]<>0 or [University]<>0

[College]<>0

[College]-={0}

in set analysis. But how about 2 fields using the OR statement to be equal to 0. I am new here and appreciate the help. Thanks.

Re: 2 fields not equal to 0 in set analysis

sunny_talwar — Mon, 27 Apr 2015 21:01:38 GMT

Try this:

{<College -= {0}>+<University -= {0}>}

Re: 2 fields not equal to 0 in set analysis

maxgro — Mon, 27 Apr 2015 21:05:09 GMT

try with +

count( {$ <College-={0}>+<University-={0}>} id_user)

Re: 2 fields not equal to 0 in set analysis

Clever_Anjos — Mon, 27 Apr 2015 21:14:50 GMT

Set Analysis uses Algebra, an OR cab be replaced with an union of two sets.

So <College-={0}>+<University-={0} is what you need

Re: 2 fields not equal to 0 in set analysis

Gabriel — Mon, 27 Apr 2015 22:07:39 GMT

Hi,

How about

{<College -= {0} , University -= {0}>}

Re: 2 fields not equal to 0 in set analysis

Anonymous — Mon, 27 Apr 2015 23:12:04 GMT

Gabriel, this will be AND, not OR:

[College]<>0 AND [University]<>0

Re: 2 fields not equal to 0 in set analysis

Anonymous — Tue, 28 Apr 2015 08:42:31 GMT

I'm not sure how count and id_user will be used in the formula.

Re: 2 fields not equal to 0 in set analysis

Anonymous — Tue, 28 Apr 2015 08:44:28 GMT

I tried it and it does not work. It shows 'Error in set modifier expression'. Thanks for your help

Re: 2 fields not equal to 0 in set analysis

Anonymous — Tue, 28 Apr 2015 08:46:08 GMT

I tried it and it does not work. Thanks for your help.

Re: 2 fields not equal to 0 in set analysis

Anonymous — Tue, 28 Apr 2015 08:47:03 GMT

Michal is right. Anyhow, I tried it and it does not work. Thanks for your help.

Re: 2 fields not equal to 0 in set analysis

Tue, 28 Apr 2015 08:56:07 GMT

try this:

{<College-={'0'}> + <University-={'0'}>}

Re: 2 fields not equal to 0 in set analysis

Tue, 28 Apr 2015 08:57:37 GMT

Hi,

Try this one

count({$<College-={"=len(trim(College))=0"}>+<University-={"=len(trim(University))=0"}>} id_user)

Re: 2 fields not equal to 0 in set analysis

ankitaag — Tue, 28 Apr 2015 09:02:10 GMT

This will work

Script:

LOAD * INLINE [

Name, College, University, Value

A, a, b, 100

B, a, 0, 220

C, 0, b, 443

D, a, b, 342

E,0,0,4545

];

sum({<College-={0},University-={0}>}Value)

Result is

Name Value

A 100

D 342

If you want to use an operator then the below expression will give you the same result as above :

sum({<College-={0}>*<University-={0}>}Value)

Re: 2 fields not equal to 0 in set analysis

Anonymous — Tue, 28 Apr 2015 09:07:12 GMT

Here is how I solved it. Instead of using the OR operator between the fields, I put the individual fields in the formula, replicated it and put OR in between the formulas.

Here is an example:

<formula

,[College]-={0}

formula>

<formula

,[University]-={0}

formula>

The Qlikview syntax checking mechanism is only accurate until a certain extent. In complex formulas like the one I was implementing, it showed error but worked. I appreciate all your guys suggestions and hope this would help others who would encounter a similar issue as me.

Re: 2 fields not equal to 0 in set analysis

Tue, 28 Apr 2015 09:09:56 GMT

I think you can try with this

{<college={'*'} - {'0'}> + <university={'*'} - {'0'}>}

Re: 2 fields not equal to 0 in set analysis

ankitaag — Tue, 28 Apr 2015 09:16:05 GMT

not working for me