topic Re: Nested If not working :( in QlikView

Nested If not working :(

Mon, 11 May 2015 09:21:28 GMT

Hi there

I need some help with a nested If statement. It's not giving me what I need. I have two main criteria : CLOSED Status and ACTIVE status. For CLOSED status there could only be two scenarios : OUT of breach and NOT breached. For ACTIVE status theres are a few scenarios. But for some reason all my ACTIVE scenarios are returned even when the Status is CLOSED. Here is my current Nested If. Or maybe you can propose another method :

IF (UPPER(Status) = 'CLOSED' And (Amended_Duration) / Incident_SLA_Target_Day * 100 >= 95, 'SLA Breached',
IF (UPPER(Status) = 'CLOSED' And (Amended_Duration) / Incident_SLA_Target_Day * 100 < 95, 'Not Breached',
IF(Incident_SLA_Target_Day_New = 'Unknown' AND Status = 'ACTIVE', 'Unknown',
If(Incident_SLA_Target_Day_New = 'Awaiting Information', 'Awaiting Information',
If (Status = 'ACTIVE' AND (Amended_Duration) / Incident_SLA_Target_Day * 100 >= 95, 'SLA Breached',
IF (Status = 'ACTIVE' And (Amended_Duration) / Incident_SLA_Target_Day * 100 >= 75 And
(Amended_Duration) / Incident_SLA_Target_Day * 100 < 95, 'Danger',
IF (Status = 'ACTIVE' And (Amended_Duration) / Incident_SLA_Target_Day * 100 < 75, 'Safe',
)
)
)
)
)
)
) As Incident_Final_SLA_Status

Re: Nested If not working :(

robert_mika — Mon, 11 May 2015 09:26:46 GMT

Could you post your data or qvw file?

Re: Nested If not working :(

Mon, 11 May 2015 09:39:37 GMT

The IF statement looks good. The only thing I observed is that you have only used UPPER(Status) when CLOSED. Why is that so?

Re: Nested If not working :(

Mon, 11 May 2015 09:44:27 GMT

Thats just one of the things I've tried when nothing else worked.... The original data only has Uppercase though, so I doubt it would make a difference.

Re: Nested If not working :(

Mon, 11 May 2015 09:45:01 GMT

Try this..

If(Wildmatch(Incident_SLA_Target_Day_New,'Unknown'),'Unknown',If(Wildmatch(Incident_SLA_Target_Day_New,'Awaiting Information'),'Awaiting Information',
If(Wildmatch(Status,'CLOSED'),if(((Amended_Duration) / Incident_SLA_Target_Day * 100)>=95,'SLA Breached','Not Breached'),
If(Wildmatch(Status,'ACTIVE'),if(((Amended_Duration) / Incident_SLA_Target_Day * 100)>=95,'SLA Breached',if(((Amended_Duration) / Incident_SLA_Target_Day * 100)<75,'Safe','Danger'

))))))

But please have a check on the brackets and syntax.

Regards,

Dawar

Re: Nested If not working :(

mrossoit — Mon, 11 May 2015 09:45:16 GMT

Hi,

it's not clear how (Amended_Duration) / Incident_SLA_Target_Day * 100 has to be considered in combination of Incident_SLA_Target_Day_New value: when 'Unknown', when 'Awaiting Information' or could it assume other values?

Here a more compact version of your expression:

IF (UPPER(Status) = 'CLOSED', if((Amended_Duration) / Incident_SLA_Target_Day * 100 >= 95, 'SLA Breached', 'Not Breached'),

IF(Incident_SLA_Target_Day_New = 'Unknown', 'Unknown',

IF(Incident_SLA_Target_Day_New = 'Awaiting Information', 'Awaiting Information',

IF((Amended_Duration) / Incident_SLA_Target_Day * 100 >= 95, 'SLA Breached',

IF((Amended_Duration) / Incident_SLA_Target_Day * 100 >= 75 And (Amended_Duration) / Incident_SLA_Target_Day * 100 < 95, 'Danger', 'Safe'

))))) As Incident_Final_SLA_Status

Regards,

Re: Nested If not working :(

sagarkharpude — Mon, 11 May 2015 09:46:26 GMT

Please attached the sample data

Re: Nested If not working :(

Mon, 11 May 2015 09:49:29 GMT

I'll give this a go quickly... Thanks Mass

Re: Nested If not working :(

Mon, 11 May 2015 09:57:37 GMT

Try this as well:

if((Amended_Duration) / Incident_SLA_Target_Day * 100 >= 95, 'SLA Breached',

if(UPPER(Status) = 'CLOSED' AND (Amended_Duration) / Incident_SLA_Target_Day * 100 < 95, 'Not Breached',

if(UPPER(Status) = 'ACTIVE' And (Amended_Duration) / Incident_SLA_Target_Day * 100 >= 75 And (Amended_Duration) / Incident_SLA_Target_Day * 100 < 95, 'Danger',

if(UPPER(Status) = 'ACTIVE' And (Amended_Duration) / Incident_SLA_Target_Day * 100 < 75, 'Safe',

If(Incident_SLA_Target_Day_New = 'Awaiting Information', 'Awaiting Information','Unknown')

)