Grouping times into intervals

[Anonymous]

Hello everyone,

I have some data that looks like this:

A_Date	A_Time	A_Code	A_Desc	A_ID
01/04/2016	00:01:00	481	Machine Alarm: 481 - Laser Scanner Trip [Lay Table]	120626
01/04/2016	00:04:13	481	Machine Alarm: 481 - Laser Scanner Trip [Unloader]	120762
01/04/2016	00:14:45	480	Machine Alarm: 480 - Scissor Lift Light Curtain is Broken - Press Blue Reset [Light Curtain Short Edge]	121288
01/04/2016	00:29:47	480	Machine Alarm: 480 - Scissor Lift Light Curtain is Broken - Press Blue Reset [Light Curtain Short Edge]	122702
01/04/2016	00:42:12	481	Machine Alarm: 481 - Laser Scanner Trip [Unloader]	122766
01/04/2016	01:02:34	481	Machine Alarm: 481 - Laser Scanner Trip [Unloader]	54818

The end goal is to analyse whether any alarms (A_Code) occur more frequently at certain times of the day through some sort of chart.

To achieve this, I need to group the times into different 4 hour buckets:

12am - 4am

4am - 8am

8am - 12pm

12pm - 4pm etc.

I know I could achieve this with IF statements within the script however I want to avoid this if possible. That final dataset will be large, perhaps around 400 million rows and will increase constantly so the solution needs to be efficient. I'm sure there is a better way to do it with either the Class() or IntervalMatch() functions however I'm struggling to see how this would work with my data as it is in time format.

Any advice would be much appreciated! 🙂

[effinty2112]

Hi Jo,

Try something like:

Data:

Load

*,

Pick(Ceil(Num(A_Time)/6),

'12am - 4am',

'4am - 8am',

'8am - 12pm',

'12pm - 4pm',

'4pm - 8pm',

'8pm - 12am') as TimePeriod;

LOAD A_Date,

     A_Time,

     A_Code,

     A_Desc,

     A_ID

FROM

[https://community.qlik.com/thread/223974]

(html, codepage is 1252, embedded labels, table is @1);

The field TimePeriod will serve as your dimension.

Kind regards

Andrew

[boorgura]

IntervalMatch should be helpful here.

Load your buckets with start and end times (as an inline table)

then load the fact table.

followed by

intervalmatch()

**Please make sure the format of the A_Time and Start and End Times are similar.

[effinty2112]

Hi Rakesh,

I think IntervalMatch is useful for the reverse situation to what we have here. IntervalMatch allows us to associate intervals in a fact table with individual elements in a dimensional table.

Here Jo is trying to associate times in the fact table to intervals.

(Of course it's always possible that I've misunderstood the question - did that before )

Kind regards

Andrew

[boorgura]

Well - how about this:

buckets:

Noconcatenate LOAD * Inline[

Start, End, Bucket

00:01, 4:00, A

4:01, 8:00, B

8:01, 12:00, C

];

Fact:

LOAD A_Time, ...

..

...

..

FROM ....;

Join IntervalMatch(A_Time) LOAD Start, End Resident buckets;

join LOAD * Resident buckets;

drop table buckets;

The above will have a column in the FACT which will have the BUCKET info. (If not needed, the interval start and End fields can be dropped)

[MarcoWedel · Accepted solution]

Hi,

maybe one solution might be:

  

Using below TimeBucket expression as calculated dimension in the chart and a variable to change the bucket width might result in a more flexible chart. But I guess this solution would lack the necessary performance given your table size:

tabAlarms:
LOAD *,
    Timestamp(A_Date+A_Time) as A_DateTime,
    Dual(Time(Floor(A_Time,'04:00'),'hhtt')&' - '&Time(Floor(A_Time,'04:00')+'04:00','hhtt'),Floor(A_Time,'04:00')) as TimeBucket,
    'Machine Alarm: '&A_Code as A_Desc;

//sample data generation
LOAD RecNo() as A_ID,
    DayName(MakeDate(2016)+Rand()*10) as A_Date,
    Time(Rand()) as A_Time,
    Num(Ceil(Rand()*10)+100,'000') as A_Code
AutoGenerate 300;

hope this helps

regards

Marco

[Anonymous]

This is great!!! Thank you!! I especially appreciate the chart examples, I wouldn't have thought of some of them. I'm going with the variable input for now as it seems to be okay performance wise and the users will love it. If it gets too slow once the full dataset is imported, I'll switch to the script solution.