Grouping Hours

[narband2778]

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

[MK_QSL · Accepted solution]

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'))

[narband2778]

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

[kakani87]

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

[narband2778]

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

[jonathandienst]

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')))

[jonathandienst]

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

..,

If(Gp_Letter_DateTime - Discharge_DateTime <= 13/24,

...

[MK_QSL]

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'))

[narband2778]

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.

[qv_testing]

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;