topic Re: Range Days in QlikView

Range Days

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

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:

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 — Thu, 04 Aug 2016 15:21:01 GMT

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 — Thu, 04 Aug 2016 15:27:33 GMT

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 — Thu, 04 Aug 2016 15:30:54 GMT

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