Issue with Grouping in Qlikview

[narband2778]

Hello Everyone,

I have used Interval() to find the number of hours between two date times, but when I am trying to group them using IF statement, result set is not looking good.

Am I doing any mistakes or being stupid.

Please find the attached Qlikview Document.

Message was edited by: Naresh Bandari

[ychaitanya]

i would recommend ..

try calculating the difference between the 2 date times in the Load Script it self and create the buckets/groups as you need with IF conditions.

Thanks

CY

[narband2778]

yeah already did that, but still the same.

Thanks,

[qv_testing · Accepted solution]

Try this,

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
];

[narband2778]

Working perfectly.

Thanks Mate

[narband2778]

Hey Raju,

Still there is some issue when i am trying to add another inline record to existing App, but I am getting the hours in Wrong group.

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

I have updated the QVW file

Thanks,

Naresh

[qv_testing]

Try this.....  here 2 solutions

1. IF condition

2. Interval Match

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;

[narband2778]

Hi Raju,

Thanks for your help.

But below one is working fine.

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

Thanks,

Naresh

[qv_testing]

Yes, i forgot to convert to Hours.

Anyway you got the solution