topic Re: advanced Set Analysis with OR??? in QlikView

advanced Set Analysis with OR???

Wed, 15 Jul 2015 10:33:56 GMT

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

Re: advanced Set Analysis with OR???

MayilVahanan — Wed, 15 Jul 2015 10:40:26 GMT

May be try like this

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

Re: advanced Set Analysis with OR???

morganaaron — Wed, 15 Jul 2015 10:41:45 GMT

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)

Re: advanced Set Analysis with OR???

Gysbert_Wassenaar — Wed, 15 Jul 2015 10:52:43 GMT

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.

Re: advanced Set Analysis with OR???

qlikviewwizard — Wed, 15 Jul 2015 13:40:00 GMT

Hi sivadandu

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

Thank you

Re: advanced Set Analysis with OR???

Wed, 15 Jul 2015 13:58:52 GMT

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...

Re: advanced Set Analysis with OR???

jonathandienst — Wed, 15 Jul 2015 14:43:57 GMT

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)