topic Operators (and and or in sql) in set analysis in QlikView

Operators (and and or in sql) in set analysis

robleab2018 — Tue, 08 May 2018 20:51:50 GMT

Hello QV community!

I'm hoping someone will help me changing BusinessObjects sql to QV set analysis. I'm having a hard time matching up data with the equivalent OR and AND operators in set analysis. Does anyone see anything that is wrong?

thanks

The records must meet the first criteria and then either the first OR stmt or the 2nd stmt.

--business objects sql query

AND scorpion.a_segatyelm.segaty_cd = 'C'

AND scorpion.a_agtdty.agt_dty_cd NOT IN ( 'R','0' )

AND scorpion.a_crs.crs_cd <> 'B'

AND scorpion.a_ascbllcls.ascbll_ind = 'Y'

AND (

scorpion.a_segsts.segsts_cd = 'NO'

( scorpion.v_prvsegsts.prvsegsts_cd = 'NO'

AND

scorpion.a_segsts.segsts_cd IN ( 'XX','XK' ) )

--qlikview set analysis with OR and AND operators

=Sum({(<agt_dty_cd={'R','0'},segaty_cd={'C'},ascbll_ind={'Y'},crs_cd-='B'>)*<cursegsts_cd={'NO'}>+<prvsegsts_cd={'NO'},cursegsts_cd={'XX','XK'}>}ar_qty)

Re: Operators (and and or in sql) in set analysis

Anil_Babu_Samineni — Wed, 09 May 2018 00:03:59 GMT

Not really sure

Sum(If((scorpion.a_segatyelm.segaty_cd = 'C' and Not Match(scorpion.a_agtdty.agt_dty_cd, 'R', '0')) and (scorpion.a_crs.crs_cd <> 'B'

and scorpion.a_ascbllcls.ascbll_ind = 'Y') and

(scorpion.a_segsts.segsts_cd = 'NO' or (scorpion.v_prvsegsts.prvsegsts_cd = 'NO' and Match(scorpion.a_segsts.segsts_cd, 'XX','XK')), ar_qty))

=Sum({(<agt_dty_cd={'R','0'},segaty_cd={'C'},ascbll_ind={'Y'},crs_cd-='B'>*<cursegsts_cd={'NO'}>+<prvsegsts_cd={'NO'},cursegsts_cd={'XX','XK'}>}ar_qty)

Re: Operators (and and or in sql) in set analysis

sunny_talwar — Wed, 09 May 2018 02:36:11 GMT

May be this

=Sum({(<agt_dty_cd={'R','0'},segaty_cd={'C'},ascbll_ind={'Y'},crs_cd-='B'>)*(<cursegsts_cd={'NO'}>+<prvsegsts_cd={'NO'},cursegsts_cd={'XX','XK'}>)} ar_qty)