Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Trying to use time intervals

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

Tags (1)
4 Replies
ift_isabelle
Not applicable

Re: Trying to use time intervals

Have you tried the function IntervalMatch?

IntervalMatch ‒ Qlik Sense

Not applicable

Re: Trying to use time intervals

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

ift_isabelle
Not applicable

Re: Trying to use time intervals

‌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..

sunny_talwar
Not applicable

Re: Trying to use time intervals

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;