Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

advanced Set Analysis with OR???


Hi,

I am looking for a solution for this kind of condition:

I tried a lot, but could not succeed to resolve this in Set Analysis.

FECDATA is the dimention and tried to create the expression with the following:

 

Sum

({<IDESCENA = {'01' },IDENTJLO = {'6120'},CODJEN2 = {'20210000'}>} xor {<CODJEN3 = {'20050000'} xor {<CODJEN3 = {'20050000'}>}ISAFCCMP)

Query:

Select sum(ISAFCCMP)

from xxxx table

where

" TD_CTL_D_BS_PL.IDESCENA = '01'        ===> Common condition of the following 2 lines

AND
(( TD_CTL_D_BS_PL.IDENTJLO = '6120' AND SI_DDJ_PGES_L.CODJEN2 = '20210000' )
OR

( SI_DDJ_PGES_L.CODJEN3 = '20050000' AND SI_DDJ_UNEG_L.CODJEN2 <> '03040000' ))"

AND
TD_CTL_D_BS_PL.FECDATA = '22-Apr-2015'    ===> this is our dimension

I really appreciate your great help....

Regards

Siva

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

You can do that - just use normal parentheses instead of curly braces:

Sum({<IDESCENA ={'01'}> * (<IDENTJLO ={'6120'}, CODJEN2 ={'20210000'}> + <CODJEN3={'20050000'}, UNEG_CODJEN2-={'03040000'}>) } ISAFCCMP)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
MayilVahanan

Hi

May be try like this

=Sum({<IDESCENA = {'01'}, IDENTJLO = {'6120'} ,CODJEN2 = {'20210000'}>+<IDESCENA = {'01'}, CODJEN3 = {'20050000'}, CODJEN2 -={'03040000'}>}ISAFCCMP)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
morganaaron
Specialist
Specialist

You need to use a set operator, in this case a union to return any records belonging to either set:

({<IDESCENA = {'01' }, IDENTJLO = {'6120'}, CODJEN2 = {'20210000'}>} + {<IDESCENA = {'01' }, CODJEN3 = {'20050000'}, CODJEN2 -= {'03040000'}>} ISAFCCMP)

Gysbert_Wassenaar

The set analysis equivalent of XOR is the / operator: sum({ <DimA={1}> / <DimB={1}> })

If you don't want XOR, but OR then use the + operator.


talk is cheap, supply exceeds demand
qlikviewwizard
Master II
Master II

Hi sivadandu

Could you attach some sample data file. It will help us to understand more.

Thank you

Not applicable
Author

Thanks tp you all for  quick response.... Is there anyway to make the first condition as common to other two conditions...

I mean, can we avoid   IDESCENA = {'01' } in both the sets (ie> writing this condition outside of the set)

  Something like this... but its not correct when closing the curly braket..

Sum

({<IDESCENA ={'01'}> * {<IDENTJLO ={'6120'},CODJEN2 ={ '20210000'}> + <CODJEN3={'20050000'},UNEG_CODJEN2-={'03040000'}>} } ISAFCCMP)

Please correct me, if you wrong...

jonathandienst
Partner - Champion III
Partner - Champion III

You can do that - just use normal parentheses instead of curly braces:

Sum({<IDESCENA ={'01'}> * (<IDENTJLO ={'6120'}, CODJEN2 ={'20210000'}> + <CODJEN3={'20050000'}, UNEG_CODJEN2-={'03040000'}>) } ISAFCCMP)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein