Analyzing time in QlikView

[#1]

Hi,

I'm working with accidents database. Each accident has time of occurring (for example: 8:02 am).

I want to analyse the data by this time but then I get many different times, and I want to make it more general (for example: all the accidents from 7:00 am to 12:00 am will be classified as "morning").

How can I do this? someone told me it could be done with ranges or something....

Thnx!

Nitzan

[disqr_rm]

Try this:

Accidents:
LOAD * INLINE [
AcID, AcDat, AcTime
1, 1/1/2010, 8:01:00
2, 1/7/2010, 9:10:11
3, 2/1/2011, 13:00:00
4, 3/4/2011, 17:00:00
];

Bucket:
LOAD * INLINE [
TimeFrom, TimeTo, TimeDesc
00:00:01, 09:00:00, Morning
09:01:01, 16:00:00, Day
16:00:01, 23:59:59, Evening
];

IntervalMatch(AcTime)
LOAD TimeFrom, TimeTo RESIDENT Bucket;

Just put all the fields on the screen and you will see what it is doing. Search here for IntervalMatch for more details on it.

Rakesh

[#3]

Hi Rakesh,

I like your clear and easy to understand exam for IntervalMatch very much (I saw a lot of complicated descriptions yet).

What do you think about joining like

Join (Bucket)
IntervalMatch(AcTime)
LOAD . . .

This simplifies the data modell and I think it should work as fine as yours.

Regards, Roland

[disqr_rm]

Hi Roland,

Yes, it's a known undocumented feature for a while and very popular. I always am very careful joining interval-matched tables, as it just multiplies number of records your main table. I am mostly working with huge data volume sourced from SAP systems, so I do have to worry about size of data and number of records in the tables.

So, if the tables (mainly fact) you are working with is not too big, sure go ahead and left join it, but keeping them as-it-is would not hurt as Synthetic keys are "ok" to have with interval-matched tables.

EDIT: By the way, you can do another left join to put all back to one table, just be careful as mentioned above.

Accidents:
LOAD * INLINE [
AcID, AcDat, AcTime
1, 1/1/2010, 8:01:00
2, 1/7/2010, 9:10:11
3, 2/1/2011, 13:00:00
4, 3/4/2011, 17:00:00
];

Bucket:
LOAD * INLINE [
TimeFrom, TimeTo, TimeDesc
00:00:01, 09:00:00, Morning
09:01:01, 16:00:00, Day
16:00:01, 23:59:59, Evening
];

LEFT Join (Accidents)
IntervalMatch(AcTime)
LOAD TimeFrom, TimeTo RESIDENT Bucket;

LEFT Join (Accidents)
LOAD * RESIDENT Bucket;

DROP Table Bucket;

[#5]

Thank you Rakesh for your quick reply

I've tried your solution and it seems to be working except from one thing -

I entered the following code:

LOAD `Acd_code`,

`Acd_Date`,

year(`Acd_Date`) as Acd_Year,

Month(`Acd_Date`) as Acd_Month,

`Acd_Description`,

`Acd_Environment` as Environment_Code ,

Time(`Acd_hour`) as Acd_Time,

`Acd_House_Num`,

`Acd_Num_of_Injureds`,

`Acd_phone`,

`Acd_Street`,

`Acd_town`,

`Reporter_ID`,

`Reporter_name`,

`Reporter_phone`,

`Reporter_role`;

SQL SELECT *

FROM Accident;

Bucket:

LOAD * INLINE [

TimeFrom, TimeTo, TimeDesc

00:00:00, 07:00:00, Night

07:00:01, 12:00:00, Day

12:00:01, 18:00:00, Noon

18:00:01, 23:59:59, Evening

];

And then I've added a pie chart with the dimension TimeDesc and the expression "count (distinct Acd_code)"

Like I said I have the accident table in my database (with all the accident details) and I also have injuries table (in this table the same accident code can appear several times - each time with th id of a different employee....in case of multiple injures accidents). the injuries table and the accident table are connected by the Acd_code. So now it seems that I get the requested time analyse (for day,noon, night and evening) but for the injuries table. This is why I get extra null values beside the correct time analyse numbers (for example: 22-day, 10-night, 15-null).

Can I resolve it somehow?

Tnanx!

Nitzan

[#6 · Accepted solution]

Hello Nitzan,

because the field "Acd_code" is a key and links two tables together, the counting isn't possible. Here comes a possible workaround:

Load your linking field in both tables A and B twice with two different names eg. Acd_code_A and Acd_code_B. And you still have the link with "Acd_code" from table A to table B.

BTW: I would rename both to $Acd_code_ID to show they are from now on (only) technical IDs to link the tables. In your charts and all other objects you can easily use (eg. count distinct ) Acd_code_A or Acd_code_B (and of course $Acd_code_ID, if that's for some reasons necessary)

Regards, Roland

[#7]

It works just fine!! Thanks to your precious advice!

Really appreciate it.

Nitzan