topic Re: how to create bucket in QlikView

how to create bucket

Sat, 27 Sep 2014 09:03:42 GMT

Hi All,

I am calculating the duration in minutes. means close date (time stamp) and enter date (time stamp) the duration is interval (close date- enter date, 'mm'). using this expression to calculate the duration in minutes. my question is how to divide the duration into buckets like 15minutes,

1 Hour,

24 Hour

> 24 Hours any one tell me plz .urgent

Re: how to create bucket

sundarakumar — Sat, 27 Sep 2014 09:15:40 GMT

use class function

PFA

-sundar

Re: how to create bucket

Anonymous — Sat, 27 Sep 2014 09:16:34 GMT

use the class function

class(interval(A,'mm' ),15)

Re: how to create bucket

Sat, 27 Sep 2014 09:31:01 GMT

how to write the class function. plz write the expression corresponding my data plz.... urgent

Re: how to create bucket

Sat, 27 Sep 2014 09:33:19 GMT

how to write the class function. plz the expression in discussion form plz urgent..

Re: how to create bucket

sundarakumar — Sat, 27 Sep 2014 10:28:51 GMT

pls post sample data..

Re: how to create bucket

geert_gelade — Sat, 27 Sep 2014 10:31:11 GMT

I have two other possible solutions:

1. using a nested if-clause to define the buckets

2. using intervalmatch with script below as example:

//First define the intervals with min and max values (table Intervals)

Intervals:

LOAD * INLINE [

min, max, bucket

0, 15, 15 minutes

16, 60, 1 hour

61, 1440, 24 hours

1441, , > 24 hours

];

//The Data table contains the minutes

Data:

LOAD * INLINE [

ID,minutes

1,1

2,15

3,16

4,1420

5,1440

6,2563

7,9999

];

//The link table by using IntervalMatch

Link:

IntervalMatch (minutes)

Load distinct min, max resident Intervals;

Re: how to create bucket

PrashantSangle — Sun, 28 Sep 2014 06:13:21 GMT

Hi,

Read this article,

http://community.qlik.com/blogs/qlikviewdesignblog/2014/07/14/buckets

It helps you.

Regards

Re: how to create bucket

MarcoWedel — Sun, 28 Sep 2014 11:21:06 GMT

Hi,

one possible solution:

LOAD *,
    If([date_closed]-[date-entered]<= 1/96,'<= 15minutes',
      If([date_closed]-[date-entered]> 1/96 and [date_closed]-[date-entered]<=1/24,'<= 1 Hr',
      If([date_closed]-[date-entered]> 1/24 and [date_closed]-[date-entered]<= 1,'<= 24 Hr',
        If([date_closed]-[date-entered]> 1,'> 24 Hr',)))) as category,
    Interval([date_closed] - [date-entered]) as duration;
LOAD serviceid,
    Alt(Timestamp#([date-entered], 'DD/MM/YYYY hh:mm[:ssTT]'), Date#([date-entered])) as [date-entered],
    Alt(Timestamp#([date_closed], 'DD/MM/YYYY hh:mm[:ss TT]'), Date#([date_closed])) as [date_closed]     
FROM [http://community.qlik.com/servlet/JiveServlet/download/620572-129168/service.xlsx] (ooxml, embedded labels, table is Sheet3);

hope this helps

regards

Marco

Re: how to create bucket

ToniKautto — Sun, 28 Sep 2014 13:52:26 GMT

I think you should go with max dreamer's article reference as basis for the considerations you need to make, as well as the different options that you have. Personally I always try to avoid IF statements as these can be come a performance burden, and harder to read and administrate.

I do not think it is fair to request an answer that is customized on your data. You need to take some time and evaluate the feedback you have received, in order to understand the implementation that you decide to make.