Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
kishoreravi1983
Contributor III
Contributor III

If Condition on 4 Columns

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

RESULTRESULT

 Please help me on this regards.

Thank you!

Ravi

 

 

Labels (1)
1 Solution

Accepted Solutions
ashok_rajm
Contributor III
Contributor III

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

 

 

View solution in original post

6 Replies
lironbaram
Partner
Partner

hi

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',

lironbaram
Partner
Partner

hi

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'

ashok_rajm
Contributor III
Contributor III

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

 

 

View solution in original post

kishoreravi1983
Contributor III
Contributor III

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,

michielvandegoo
Specialist
Specialist

manoranjan_321988
Contributor
Contributor

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