topic Issue with Grouping in Qlikview in QlikView

Issue with Grouping in Qlikview

narband2778 — Wed, 04 Apr 2018 13:08:00 GMT

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

Re: Issue with Grouping in Qlikview

ychaitanya — Wed, 04 Apr 2018 13:13:00 GMT

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

Re: Issue with Grouping in Qlikview

narband2778 — Wed, 04 Apr 2018 13:16:39 GMT

yeah already did that, but still the same.

Thanks,

Re: Issue with Grouping in Qlikview

Nagaraju_KCS — Wed, 04 Apr 2018 13:30:41 GMT

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

Re: Issue with Grouping in Qlikview

narband2778 — Wed, 04 Apr 2018 13:57:29 GMT

Working perfectly.

Thanks Mate

Re: Issue with Grouping in Qlikview

narband2778 — Mon, 09 Apr 2018 10:42:39 GMT

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

Re: Issue with Grouping in Qlikview

Nagaraju_KCS — Mon, 09 Apr 2018 12:43:58 GMT

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;

Re: Issue with Grouping in Qlikview

narband2778 — Mon, 09 Apr 2018 13:06:37 GMT

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

Re: Issue with Grouping in Qlikview

Nagaraju_KCS — Mon, 09 Apr 2018 13:08:37 GMT

Yes, i forgot to convert to Hours.

Anyway you got the solution