topic Re: Convert IF into SET analysis in QlikView

Convert IF into SET analysis

shanky1907 — Tue, 09 Sep 2014 10:15:00 GMT

I have this code which is written with IF condition, Please help me with converting it into SET analysis

=Count((IF(STATUS<>'Cancelled' Or STATUS<>'Expired' or STATUS<>'Pending Expiration',

IF(REG_Flag>=1 or MED_Flag>=1 or LEG_Flag>=1 ,If(CalendarYear=$(vCurrentYear),object_name)))))

Any leads will be helpful.

Re: Convert IF into SET analysis

simondachstr — Tue, 09 Sep 2014 10:18:29 GMT

Hi Shashank,

This statement doesn't make sense to me as they cancel each other out. Are you sure it's not an AND statement?

STATUS<>'Expired' or STATUS<>'Pending Expiration'

Re: Convert IF into SET analysis

manideep78 — Tue, 09 Sep 2014 10:24:50 GMT

Hi Shashank

You wrote only true condition in the above mentioned IF statement. So my doubt is what if the above condition is false. But I understood that you are taking only true conditions to get some value. please try the below expression which I wrote from the above situation with little understanding. It could be better if you can elaborate more.

Count({<STATUS-={'Cancelled','Expired', 'Pending Expiration'},

REG_Flag ={">=1"}, MED_Flag={">=1"}, LEG_Flag={">=1"},

CalendarYear={$(vCurrentYear)} >}object_name)

Hope this helps.

Regards,
Manideep

Re: Re: Convert IF into SET analysis

israrkhan — Tue, 09 Sep 2014 10:37:26 GMT

=Count((IF(STATUS<>'Cancelled' Or STATUS<>'Expired' or STATUS<>'Pending Expiration',

IF(REG_Flag >=1 or MED_Flag >=1 or LEG_Flag >=1 ,If(CalendarYear=$(vCurrentYear),object_name)))))

try like below...

Count({<STATUS -={'Cancelled' , 'Expired' , 'Pending Expiration'},

REG_Flag = {">= 1"}, MED_Flag={">=1"}, LEG_Flag={">=1"},

CalendarYear ={$(vCurrentYear)} >} object_name)

Re: Convert IF into SET analysis

qlikoqlik — Tue, 09 Sep 2014 10:38:52 GMT

Try the below, in qlikview you have add a minus sign infront of = to mean not equal to

Count({<STATUS-={'Cancelled','Expired','Pending Expiration'},

REG_Flag -={'1'}, MED_Flag-={'1'}, LEG_Flag-={'1'}>}

CalendarYear={$(vCurrentYear)} object_name)

Regards

Padma

Re: Convert IF into SET analysis

maxgro — Tue, 09 Sep 2014 10:41:19 GMT

count({$

<STATUS=-{'Cancelled','Expired','Pending Expiration'},REG_Flag={">1"}>*<CalendarYear={$(vCurrentYear)}>

+<STATUS=-{'Cancelled','Expired','Pending Expiration'},MED_Flag={">1"}>*<CalendarYear={$(vCurrentYear)}>

+<STATUS=-{'Cancelled','Expired','Pending Expiration'},LEG_Flag={">1"}>*<CalendarYear={$(vCurrentYear)}>

} object_name)

Re: Convert IF into SET analysis

senpradip007 — Tue, 09 Sep 2014 10:42:41 GMT

Try this

Count({<STATUS -={'Cancelled','Expired','Pending Expiration'}, CalendarYear={'$(vCurrentYear)'}, REG_Flag -={1}>} object_name)

Count({<STATUS -={'Cancelled','Expired','Pending Expiration'}, CalendarYear={'$(vCurrentYear)'}, MED_Flag -={1}>} object_name)

Count({<STATUS -={'Cancelled','Expired','Pending Expiration'}, CalendarYear={'$(vCurrentYear)'}, LEG_Flag -={1}>} object_name)

Re: Convert IF into SET analysis

israrkhan — Tue, 09 Sep 2014 10:44:18 GMT

qlikOqlik

REG_Flag -={'1'}, MED_Flag-={'1'}, LEG_Flag-={'1'}......???

what if he has REG_Flag =0 or -1?, and MED_Flag= 0 or -1 ? and LEG_Flag = 0 or -1 ???

Re: Convert IF into SET analysis

shanky1907 — Tue, 09 Sep 2014 10:52:34 GMT

yes...if any of the statement occurs... then the condition must be false