Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
bobbydave
Creator III
Creator III

Nested if statement issue

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

1 Solution

Accepted Solutions
andrey_krylov
Specialist
Specialist

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)],

View solution in original post

5 Replies
jobsonkjoseph
Creator III
Creator III

Hi,

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

bobbydave
Creator III
Creator III
Author

This is part of my script.

Nothing complicated with Q and P. These are dates.

andrey_krylov
Specialist
Specialist

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

andrey_krylov
Specialist
Specialist

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)],

PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂