6 Replies Latest reply: Apr 17, 2011 3:26 PM by cybernit RSS

    Analyzing  time in QlikView

    cybernit

      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

        • Analyzing  time in QlikView
          Rakesh Mehta

          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

            • AW:Re: Analyzing  time in QlikView

              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


                • AW:Re: Analyzing  time in QlikView
                  Rakesh Mehta

                  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;


                    • AW:Re: Analyzing  time in QlikView
                      cybernit

                      Thank you Rakesh for your quick reply Smile

                      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

                        • AW:Re: AW:Re: Analyzing  time in QlikView

                          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