4 Replies Latest reply: Sep 15, 2016 2:04 PM by Sunny Talwar RSS

    Trying to use time intervals

    denis Brennan

      Good afternoon,

       

      I am new to Qlik Sense Desktop but am pleased with my progress and very impressed with its power. However, I confess I am stuck on this point. I have one facts table and several dimension tables and all seems to work well except that I need to look up "timeslots".

      The facts table looks like this: IN fact it contains UK "half hourly data consumption"

       

          

      MPANEvent DateTimeRAGidkWh
      210004103041301/01/201400:00Wed00:0031.2
      210004103041301/01/201400:30Wed00:3032.2
      210004103041301/01/201401:00Wed01:0033.6
      210004103041301/01/201401:30Wed01:3031.6
      210004103041301/01/201402:00Wed02:0032.2
      210004103041301/01/201402:30Wed02:3031.4
      210004103041301/01/201403:00Wed03:0030.8
      210004103041301/01/201403:30Wed03:3031.4
      210004103041301/01/201404:00Wed04:0030.8
      210004103041301/01/201404:30Wed04:3030

       

      I have another table at present designed like this.

           

      Wed00:0000:0007:00GREEN
      Wed07:3007:3015:30AMBER
      Wed16:0016:0019:00RED

           

      I want to link the files so that I can identify the consumption on Weds 01/01/14 at 00:00 as "green" but the consumption at 08:30 (not shown in facts table above) as "Amber".

       

      Can someone help, please?

       

      Many thanks

      Denis

        • Re: Trying to use time intervals
          Isabelle Timmermans

          Have you tried the function IntervalMatch?

          IntervalMatch ‒ Qlik Sense

            • Re: Trying to use time intervals
              denis Brennan

              So far I haven't used Data Load Editor except to make connections I have only used expressions in charts. I don't think I can use interval much in chart expressions so does that mean I have to use Data Load Editor and will that have an impact on my using DataManager? I think it's just a lack of confidence at present. Many thanks

                • Re: Trying to use time intervals
                  Isabelle Timmermans

                  If you really want to link the tables, you must do it in the dataloadeditor. If you know a bit of SQL and some formulas from excel, it's pretty easy. If you want help with it, you can upload your QVD at the forum and I will take a look at it.

                   

                  If you just want to know if a specific timeslot is green, amber or red, then I think you can use an IF function in an expression as well. I haven't tried it myself, but I guess it should look something like this:

                  If(time<7,'green',if(time<15:30,'Amber','Red')). I know this works in colorexpressions, so I guess it will work in variabele as well. But I'm not 100% sure..

                  • Re: Trying to use time intervals
                    Sunny Talwar

                    Try this script:

                     

                    Table1:

                    LOAD

                        MPAN,

                        "Event Date",

                        Time(Time#("Time", 'hh:mm')) as Time,

                        RAGid,

                        kWh

                    FROM [lib://Web]

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

                     

                    Table2:

                    LOAD

                      Time(Time#(@2, 'hh:mm')) as Start,

                        Time(Time#(@3, 'hh:mm')) as End,

                        @4 as Color

                    FROM [lib://Web]

                    (html, codepage is 1252, no labels, table is @2);

                     

                    Left Join (Table1)

                    IntervalMatch(Time)

                    LOAD Start,

                      End

                    Resident Table2;

                     

                    Left Join (Table1)

                    LOAD *

                    Resident Table2;

                     

                    DROP Table Table2;