Range Days

[microwin88x]

Hello,

I have the following table

TICKET	CREATE_DATE	CREATE_TIME	CLOSE_DATE	CLOSE_TIME	DURATION
ID1	07/06/2016	13:22:30	09/06/2016	16:10:26	02 02:47:56
ID2	07/06/2016	13:12:48	07/06/2016	14:22:28	00 01:09:40
ID4	07/06/2016	13:21:17	14/06/2016	10:36:30	06 21:15:13
ID5	08/06/2016	9:05:42	24/06/2016	13:10:21	16 04:04:39
ID6	08/06/2016	9:30:46	08/06/2016	9:40:14	00 00:09:28

What I need is to use the field DURATION (DD hh:mm:ss) to create a new field RANGE_DAYS to know if the ID corresponds to:

• <=1 day
• 2-7 days
• 8-14 days
• >= 15 days

Then I'd have something like the following:

TICKET	CREATE_DATE	CREATE_TIME	CLOSE_DATE	CLOSE_TIME	DURATION	RANGE
ID1	07/06/2016	13:22:30	09/06/2016	16:10:26	02 02:47:56	2-7 days
ID2	07/06/2016	13:12:48	07/06/2016	14:22:28	00 01:09:40	<=1 day
ID4	07/06/2016	13:21:17	14/06/2016	10:36:30	06 21:15:13	2-7 days
ID5	08/06/2016	9:05:42	24/06/2016	13:10:21	16 04:04:39	>=15 days
ID6	08/06/2016	9:30:46	08/06/2016	9:40:14	00 00:09:28	<=1 day

Do you know how to get this?

Thanks

[sunny_talwar · Accepted solution]

May be this:

Table:

LOAD *,

  If(DURATION <= 1, Dual('<=1 day', 1),

     If(DURATION <= 7, Dual('2-7 days', 2),

     If(DURATION <= 14, Dual('8-14 days', 3), Dual('>= 15 days', 4)))) as RANGE;

LOAD TICKET,

     CREATE_DATE,

     CREATE_TIME,

     CLOSE_DATE,

     CLOSE_TIME,

     Interval(CLOSE_DATE + CLOSE_TIME - CREATE_DATE - CREATE_TIME, 'D hh:mm:ss') as DURATION

FROM

[https://community.qlik.com/thread/227948]

(html, codepage is 1252, embedded labels, table is @1);

[vishsaggi]

I am not sure if this is the good way of doing it but just tried please check this ?

Dimensions: All your fields

Expression:

= IF(LEFT(DURATION,2) <= 1, '<=1Day',

  IF(Left(DURATION,2) <= 7, '2-7Days',

  IF(Left(DURATION,2) > 7, '>=15Days')))

[vishsaggi]

Oops I missed that 7-14 days. Superb Sunny.