Trying to use time intervals

[#1]

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"

MPAN	Event Date	Time	RAGid	kWh
2100041030413	01/01/2014	00:00	Wed00:00	31.2
2100041030413	01/01/2014	00:30	Wed00:30	32.2
2100041030413	01/01/2014	01:00	Wed01:00	33.6
2100041030413	01/01/2014	01:30	Wed01:30	31.6
2100041030413	01/01/2014	02:00	Wed02:00	32.2
2100041030413	01/01/2014	02:30	Wed02:30	31.4
2100041030413	01/01/2014	03:00	Wed03:00	30.8
2100041030413	01/01/2014	03:30	Wed03:30	31.4
2100041030413	01/01/2014	04:00	Wed04:00	30.8
2100041030413	01/01/2014	04:30	Wed04:30	30

I have another table at present designed like this.

Wed00:00	00:00	07:00	GREEN
Wed07:30	07:30	15:30	AMBER
Wed16:00	16:00	19:00	RED

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

[ift_isabelle]

Have you tried the function IntervalMatch?

IntervalMatch ‒ Qlik Sense

[#3]

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]

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

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;