topic Re: Interval Function with IF condition in QlikView

Interval Function with IF condition

karthikoffi27se — Sat, 16 Nov 2024 04:54:00 GMT

I have a field which is in DD:hh:mm format and I am trying to bucket into different categories as mentioned below

LOAD *,
If(LOB = 'Medical',
IF(TAT_MOD>7,'>7 days',
IF(TAT_MOD>5 AND TAT_MOD<=7, '>5 days <=7 days',
IF(TAT_MOD>2 AND TAT_MOD<=5,'>2 days <=5 days',
IF(TAT_MOD>0 AND TAT_MOD<=1,'>0 days <=1 days',
If(TAT_MOD > 4/24 AND TAT_MOD<=2, '>4 hours days <=2 days', '<= 4 hours'))))),
IF(LOB = 'Life',
IF(TAT_MOD>7,'>7 days',
IF(TAT_MOD>5 AND TAT_MOD<=7, '>5 days <=7 days',
IF(TAT_MOD>3 AND TAT_MOD<=5,'>3 days <=5 days',
IF(TAT_MOD>0 AND TAT_MOD<=1,'>0 days <=1 days',
If(TAT_MOD > 4/24 AND TAT_MOD<=3, '>4 hours days <=3 days', '<= 4 hours'))))),
IF(LOB = 'Hala',
IF(TAT_MOD>7,'>7 days',
IF(TAT_MOD>5 AND TAT_MOD<=7, '>5 days <=7 days',
IF(TAT_MOD>3 AND TAT_MOD<=5,'>3 days <=5 days',
IF(TAT_MOD>0 AND TAT_MOD<=1,'>0 days <=1 days',
If(TAT_MOD > 4/24 AND TAT_MOD<=3, '>4 hours days <=3 days', '<= 4 hours'))))),
IF(LOB = 'Liabilities',
IF(TAT_MOD>7,'>7 days',
IF(TAT_MOD>5 AND TAT_MOD<=7, '>5 days <=7 days',
IF(TAT_MOD>3 AND TAT_MOD<=5,'>3 days <=5 days',
IF(TAT_MOD>0 AND TAT_MOD<=1,'>0 days <=1 days',
If(TAT_MOD > 4/24 AND TAT_MOD<=3, '>4 hours days <=3 days', '<= 4 hours'))))),
IF(LOB = 'PA',
IF(TAT_MOD>7,'>7 days',
IF(TAT_MOD>5 AND TAT_MOD<=7, '>5 days <=7 days',
IF(TAT_MOD>3 AND TAT_MOD<=5,'>3 days <=5 days',
IF(TAT_MOD>0 AND TAT_MOD<=1,'>0 days <=1 days',
If(TAT_MOD > 4/24 AND TAT_MOD<=3, '>4 hours days <=3 days', '<= 4 hours'))))),
IF(LOB = 'Travel',
IF(TAT_MOD>7,'>7 days',
IF(TAT_MOD>5 AND TAT_MOD<=7, '>5 days <=7 days',
IF(TAT_MOD>3 AND TAT_MOD<=5,'>3 days <=5 days',
IF(TAT_MOD>0 AND TAT_MOD<=1,'>0 days <=1 days',
If(TAT_MOD > 4/24 AND TAT_MOD<=3, '>4 hours days <=3 days', '<= 4 hours'))))))))))) AS TAT_MODIFIED,

;
LOAD *,

INTERVAL#(TAT,'DD:hh:mm') AS TAT_MOD
;

Now my issue is TAT_MODIFIED is not picking up the conditional statement >4 hours days <=3 days nor >4 hours days <=2 days , it is starting show the values greater than equal to 1 day.

Any help is much appreciated.

Re: Interval Function with IF condition

karthikoffi27se — Tue, 25 Dec 2018 11:34:51 GMT

As an example

Re: Interval Function with IF condition

Anil_Babu_Samineni — Tue, 25 Dec 2018 12:17:22 GMT

Please post few records from TAT_MOD?? and explain, How you want to create flag?

Re: Interval Function with IF condition

karthikoffi27se — Tue, 25 Dec 2018 12:30:57 GMT

TAT MOD field has value as shown in the table below. Now, I want a field that is bucketed as shown in the table below

Bucketed Field

<= 4 hours

>0 days <=1 days

>2 days <=5 days

>3 days <=5 days

>4 hours <=2 days

>4 hours <=3 days

>5 days <=7 days

>7 days

TAT MOD field

0:0:00

0:0:01

0:0:02

0:0:03

0:0:04

0:0:05

0:0:06

0:0:07

0:0:08

0:0:09

0:0:10

0:0:11

0:0:12

0:0:13

0:0:14

0:0:15

Re: Interval Function with IF condition

Gysbert_Wassenaar — Tue, 25 Dec 2018 12:34:13 GMT

Of course that won't be picked up. You first test for between 0 and 1 day. And 4 hours fits in that interval of 0-1 days. The best you can hope for is that values between 1 day and 2 days get picked up for Medical and between 1 and 3 days for the other LOB values. The rest is already covered by the other intervals.

Re: Interval Function with IF condition

Anil_Babu_Samineni — Tue, 25 Dec 2018 23:57:16 GMT

@karthikoffi27se I didn't feel, That data is suffice to test.

Re: Interval Function with IF condition

karthikoffi27se — Wed, 26 Dec 2018 04:42:48 GMT

Attached the excel file to test it.

Many Thanks

Karthik

Re: Interval Function with IF condition

Dataintellinalytics — Thu, 14 Mar 2024 10:41:57 GMT

If you want to compare TAT_MOD with hour say if you want to check whether TAT_MOD > 4 hrs then you can try either of the below

If(TAT_MOD > '04:00:00' , 'Pass', 'Fail')

use Num, which will return day, multiply it with 24 to get hours.

If( Num(TAT_MOD) *24 > 4 , 'Pass' , 'Fail')