topic Nested if statement issue in QlikView

Nested if statement issue

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

Can anyone see my issue? I don't know if it is down to brackets.

if( Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') = 0.0, '0',
       if( Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') >= 0.1 and Interval(Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') <= 3.9, 'Best Effort',
            if( Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') >=4.0 and Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') <=6.9, 'Less than 90 days',
                 if( Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') >= 7.0 and Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') <=8.9, 'Less than 30 days',
                        if( Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') >=9.0 and Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') <=10.0, 'Less than 14 days',

'Over 90 Days')))))
as [Upper TIme Limit (days)],

I can get two results. 0 and 'Over 90 Days' but cant get the results from the nested if statements within the statement.

There should be multiple Less Than 30 days, Less than 14 days etc.

Please help

Re: Nested if statement issue

jobsonkjoseph — Thu, 01 Nov 2018 09:10:52 GMT

Hi,

Is this the complete script or part of it, i'm trying to replicate it but not happening.

Re: Nested if statement issue

bobbydave — Thu, 01 Nov 2018 09:15:09 GMT

This is part of my script.

Nothing complicated with Q and P. These are dates.

Re: Nested if statement issue

andrey_krylov — Thu, 01 Nov 2018 09:20:38 GMT

Hi David. Looks like syntax is fine and even checked. Make sure Q and P have a date format.

Re: Nested if statement issue

andrey_krylov — Thu, 01 Nov 2018 09:50:59 GMT

The only thing I did not get is

= 0, '0',

< 4, 'Best Effort',

< 7, 'Less than 90 days'

< 9, 'Less than 30 days'

< 10, 'Less than 14 days'

and I would simplify

if( Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') > 10, 'Over 90 Days',

if( Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') > 9 , 'Less than 14 days',

if( Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') > 7 , 'Less than 30 days',

if( Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') > 4 , 'Less than 90 days',

if( Interval (Date(Q, 'DD/MM/YYYY') - Date(P, 'DD/MM/YYYY'), 'D') > 0 , 'Best Effort'

, '0'))))) as [Upper TIme Limit (days)],

Re: Nested if statement issue

PrashantSangle — Thu, 01 Nov 2018 10:19:57 GMT

Interval() is formatting function.

Try with num#() before interval()

like if(num#(interval(date1-date2,'D'))=0,'0',

if(num#(interval(date1-date2,'D'))>0.1,'0',

)

One small question, How date difference will come in decimal format, If you are presenting it in only days?

Regards,