Multiple records in IntervalMatc why?

narayanaqlik — Wed, 04 Mar 2020 13:46:16 GMT

Hi All,

I have requirement to map timestamp to 24 hours in a day like 0,1,2,3,4,5...23 hours in X-Axis.

i have START_, STOP date fields with Timestamps

so here i ma creating one Inline table like

Event_Table:

Load * Inline [

Time, Event

00:59, 0

01:59,1

02:59,2

23:59,23

];

Interval_Table:

START_, STOP

2018-02-16T13:29:00, 2018-02-16T13:45:00,

2018-02-16T13:32:00, 2018-02-16T13:54:00

Here i ma writing

Inner join

IntervalMatc(Time)

Load START_,STOP Resident Interval_Table;

here i am getting result Every record on Interval_Table is mapping to Every record of Event_Table table .

Why it is happening like this what is the wrong here

Could you any one guide me here

very Thanks

Re: Multiple records in IntervalMatc why?

jochem_zw — Wed, 04 Mar 2020 14:17:39 GMT

Your Interval table is strange, there is overlap in times

Create an inline Interval table like this, think this is what you need:

Event_Table:
Load * Inline [
Time, Event
00:59,0
01:59,1
02:59,2
23:59,23
];

Interval_Table:
Load * Inline [
START,STOP,FromTo
00:00:00,01:00:00,0-1
01:00:01,02:00:00,1-2
];

Left join(Event_Table)
IntervalMatch(Time)
Load START,STOP Resident Interval_Table;

Left join(Event_Table)
Load START,STOP,FromTo Resident Interval_Table;

Drop Table Interval_Table;