topic Re: Grouping Hours in QlikView

Grouping Hours

narband2778 — Mon, 09 Apr 2018 11:11:32 GMT

Hello All,

I am trying to group/Bucket Hours.

If Hours_Diff is negative value should be 'Negative'.

If Hours_Diff is <= 24 hours should be grouped as 'within 24 hours'.

If Hours_Diff is > 24 hours should be grouped as 'after 24 hours'. else NULL.

Please find the attached qvw file.

Thanks,

Naresh

Re: Grouping Hours

MK_QSL — Mon, 09 Apr 2018 11:22:02 GMT

IF(Interval(Gp_Letter_DateTime-Discharge_DateTime)*24 < 0,'Negative',

IF(Interval(Gp_Letter_DateTime-Discharge_DateTime)*24 <= 24,'Withing 24 hour',

'After 24 hour'))

Re: Grouping Hours

narband2778 — Mon, 09 Apr 2018 12:02:54 GMT

Hey Manish,

Thanks this is working fine.

But, can you let me know, what's wrong the below.

=If(Interval(Gp_Letter_DateTime - Discharge_DateTime, 'hh') <0, 'Negative',

If(Interval(Gp_Letter_DateTime - Discharge_DateTime, 'hh')<= 24, 'within 24 hours',

IF(Interval(Gp_Letter_DateTime - Discharge_DateTime, 'hh')> 24, 'after 24 hours', 'Not sent')))

Thanks,

Naresh

Re: Grouping Hours

kakani87 — Mon, 09 Apr 2018 12:03:37 GMT

Check this application with included As suggested by Manish as well.

Re: Grouping Hours

narband2778 — Mon, 09 Apr 2018 12:06:55 GMT

Ya I included as suggested by Manish.

to my knowledge I was asking him why the below will not work, if I am grouping by hours.

=If(Interval(Gp_Letter_DateTime - Discharge_DateTime, 'hh') <0, 'Negative',

If(Interval(Gp_Letter_DateTime - Discharge_DateTime, 'hh')<= 24, 'within 24 hours',

IF(Interval(Gp_Letter_DateTime - Discharge_DateTime, 'hh')> 24, 'after 24 hours', 'Not sent')))

Thanks,

Naresh

Re: Grouping Hours

jonathandienst — Mon, 09 Apr 2018 12:16:05 GMT

The interval function is redundant here (its a formatting function that does not change the underlying numerical values). The time/date values are in units of days, not hours, so this whould work:

=If(Gp_Letter_DateTime - Discharge_DateTime < 0, 'Negative',

If(Gp_Letter_DateTime - Discharge_DateTime <= 1, 'within 24 hours', // 1 = 1 day = 24 hours

IF(Gp_Letter_DateTime - Discharge_DateTime > 1, 'after 24 hours', 'Not sent')))

Re: Grouping Hours

jonathandienst — Mon, 09 Apr 2018 12:17:28 GMT

And if you want a different criterion, say 13 hours, then you need something like

..,

If(Gp_Letter_DateTime - Discharge_DateTime <= 13/24,

...

Re: Grouping Hours

MK_QSL — Mon, 09 Apr 2018 12:19:24 GMT

You are comparing Hours... Interval function not directly giving you hours... so you have to either convert it to hour by multiplying it with 24 or use as below.

IF(Hours_Diff < 0,'Negative',

IF(Hours_Diff <= 1,'Within 24 hours','After 24 hours'))

Re: Grouping Hours

narband2778 — Mon, 09 Apr 2018 12:20:55 GMT

Hi jonathan,

I have tried this, suggested by qv_testing

No Luck

Table1:
Load *, IF(Time#(Hours,'hh')<0, 'Negative',
if(Time#(Hours,'hh')<=24, ' within 24 hours',
if(Time#(Hours,'hh')>24, 'after 24 Hours', 'Not set'))) as Interval;
Load
Interval(Timestamp(Gp_Letter_DateTime) - Timestamp(Discharge_DateTime)) as Hours,
Timestamp(Gp_Letter_DateTime) as Gp_Letter_DateTime,
Timestamp(Discharge_DateTime) as Discharge_DateTime;
LOAD * Inline [
ID, Gp_Letter_DateTime, Discharge_DateTime
29, 02/01/2018 09:27:00, 02/01/2018 09:30:00
30, 28/01/2018 16:38:00, 28/01/2018 13:09:00
31, 23/03/2018 09:38:00, 26/02/2018 17:20:00
32, 15/03/2018 10:20:00, 13/03/2018 18:27:00
];

Can you please look into the attached QVW file, it's not working.

Re: Grouping Hours

Nagaraju_KCS — Mon, 09 Apr 2018 12:45:53 GMT

Hi Naresh,

Try This...

Load *, IF(Time(Hours,'hh')<0, 'Negative',

if(Time(Hours,'hh')<=24, ' within 24 hours',

if(Time(Hours,'hh')>24, 'after 24 Hours', 'Not set'))) as Interval;

Load

Interval(Timestamp#(Gp_Letter_DateTime) - Timestamp#(Discharge_DateTime))*24 as Hours,

Timestamp(Gp_Letter_DateTime) as Gp_Letter_DateTime,

Timestamp(Discharge_DateTime) as Discharge_DateTime;

LOAD * Inline [

ID, Gp_Letter_DateTime, Discharge_DateTime

29, 02/01/2018 09:27:00, 02/01/2018 09:30:00

30, 28/01/2018 16:38:00, 28/01/2018 13:09:00

31, 23/03/2018 09:38:00, 26/02/2018 17:20:00

32, 15/03/2018 10:20:00, 13/03/2018 18:27:00

33, 22/11/2017 12:28:00, 24/08/2017 22:00:00

];

Buckets:

LOAD * INLINE [

Start,End, Age Group

-0.1, -0, Negative

0, 24, with in 24 hours

24, 100000, after 24 hours

];

inner join

IntervalMatch(Hours) Load Start,End

Resident Buckets;