topic If Condition on 4 Columns in QlikView

If Condition on 4 Columns

kishoreravi1983 — Tue, 27 Nov 2018 07:15:16 GMT

Hi Friends,

Please help me write a if condition for below scenario.

Attached the Data file.

Currenty i have tried this way..but its not coming correct:

If(ScopingComplete='Yes' or GovernanceComplete ='Yes' and ScopingStartDate= Null(),'Gray',
If(ScopingComplete='No' or GovernanceComplete ='No' and ScopingStartDate= Null(),'Blue',
If(ScopingComplete='No' or GovernanceComplete ='Yes' and NetWorkDays(ScopingStartDate, Today())>=0 and NetWorkDays(ScopingStartDate, Today()) <=10,'Green',
If(ScopingComplete='Yes' or GovernanceComplete ='No' and NetWorkDays(ScopingStartDate, Today())>10 and NetWorkDays(ScopingStartDate, Today())<=25,'Orange', 'Red')))) as NEW,

What i am getting is only two values in below table

RESULT

Please help me on this regards.

Thank you!

Ravi

Re: If Condition on 4 Columns

lironbaram — Tue, 27 Nov 2018 08:00:40 GMT

you only get the two first values because you have the for option covered in the first two statements

i assume you wanted to group the or statement and than check the and statement

so you need to warp the or statement in brackets like this

f((ScopingComplete='Yes' or GovernanceComplete ='Yes' )and ScopingStartDate= Null(),'Gray',

Re: If Condition on 4 Columns

lironbaram — Tue, 27 Nov 2018 08:01:17 GMT

you only get the two first values because you have the for option covered in the first two statements

i assume you wanted to group the or statement and than check the and statement

so you need to warp the or statement in brackets like this

if((ScopingComplete='Yes' or GovernanceComplete ='Yes' )and ScopingStartDate= Null(),'Gray'

Re: If Condition on 4 Columns

ashok_rajm — Tue, 27 Nov 2018 08:17:07 GMT

You can try below syntax. This might help.

If(ScopingComplete='Yes' and GovernanceComplete ='Yes' and IsNull(ScopingStartDate),'Gray',
If(ScopingComplete='No' and GovernanceComplete ='No' and IsNull(ScopingStartDate),'Orange',
If(ScopingComplete='Yes' and GovernanceComplete ='No' and IsNull(ScopingStartDate),'Blue',
If(ScopingComplete='No' and GovernanceComplete ='Yes' and IsNull(ScopingStartDate),'Blue',
if(NetWorkDays(ScopingStartDate,Today())<=10,'Green',
if(NetWorkDays(ScopingStartDate,Today())>10 and Days<=25,'Orange',
if(NetWorkDays(ScopingStartDate,Today())>25,'Red'))))))) as New

Re: If Condition on 4 Columns

kishoreravi1983 — Tue, 27 Nov 2018 08:46:15 GMT

Hi Ashok,
with a Bit change in the code the solution is working fine now. Thanks a lot for quick reply
If(ScopingComplete='Yes' and GovernanceComplete ='Yes' ,'Gray',
If(ScopingComplete='Yes' and GovernanceComplete ='No' and IsNull(ScopingStartDate),'Blue',
If(ScopingComplete='No' and GovernanceComplete ='Yes' and IsNull(ScopingStartDate),'Blue',
If(ScopingComplete='No' and GovernanceComplete ='No' and IsNull(ScopingStartDate),'Blue',
If(NetWorkDays(ScopingStartDate, Today())>=0 and NetWorkDays(ScopingStartDate, Today()) <=10,'Green',
If(NetWorkDays(ScopingStartDate,Today())>10 and NetWorkDays(ScopingStartDate, Today())<=25,'Orange',
If(NetWorkDays(ScopingStartDate,Today())>25,'Red'))))))) as NEW,

Re: If Condition on 4 Columns

Michiel_QV_Fan — Tue, 27 Nov 2018 10:51:13 GMT

http://www.qlikblog.at/1384/set-analysis-wizard-qlikview/

Re: If Condition on 4 Columns

manoranjan_321988 — Tue, 27 Nov 2018 11:57:42 GMT

the reason why its faling is because you are using OR

example 1

0 or 1 then 1->true

1 or 0 then 1 ->true

always your condition will be true

so if you use and

then it will be good

example 2:

0 and 1 only if condition true it will be true or else it will be false

1 and 0 only if condition true it will be true or else it will be false

Regards

Mano