Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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'))
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'))
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
Check this application with included As suggested by Manish as well.
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
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')))
And if you want a different criterion, say 13 hours, then you need something like
..,
If(Gp_Letter_DateTime - Discharge_DateTime <= 13/24,
...
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'))
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.
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;