How add a flag column for an event in a interval

[canmarroig]

Can you help me to create a 'flag'. I've 2 tables, the first with a list of events starting and ending, and a second table that records every 5 minutes some KPI.  I need to check on the second table when my KPI runs during first table event and mark with the id event

Table 1
id  DATE from                  DATE TO
1    29/12/2018 11.00    29/12/2018 12.00
2    29/12/2018 14.00    29/12/2018 14.25
3    30/12/2018 14.00    30/12/2018 19.00

Table 2
EVENT                           FLAG_EVENT_ID
29/12/2018 14.00      2
29/12/2018 14.05      2
29/12/2018 14.10      2
29/12/2018 14.15      2
29/12/2018 14.20      2
29/12/2018 14.25      2
29/12/2018 14.30      -

Tks so much 🙂

[sunny_talwar]

Have you already tried Interval Match function?

Interval Match

[hari8088]

By using Interval match function you can achieve this scenario.

IntervalMatch:

IntervalMatch (EVENT) LOAD DATE from, DATE TO Resident Table 1;

[Thiago_Justen_]

What about this:

Table_1:
LOAD
id,
Timestamp(TimeStamp#(DATE_from,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as DATE_from,
Timestamp(TimeStamp#(DATE_TO,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as DATE_TO;
LOAD * Inline [
id, DATE_from, DATE_TO
1, "29/12/2018 11:00", "29/12/2018 12:00"
2, "29/12/2018 14:00", "29/12/2018 14:25"
3, "30/12/2018 14:00", "30/12/2018 19:00"
];

Table_2:
LOAD
FLAG_EVENT_ID,
Timestamp(TimeStamp#(EVENT,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as EVENT;
LOAD * Inline [
EVENT, FLAG_EVENT_ID
"29/12/2018 14:00", 2
"29/12/2018 14:05", 2
"29/12/2018 14:10", 2
"29/12/2018 14:15", 2
"29/12/2018 14:20", 2
"29/12/2018 14:25", 2
"29/12/2018 14:30", -
];

LEFT JOIN (Table_2)

IntervalMatch (EVENT)
LOAD
DATE_from,
DATE_TO
Resident Table_1;

Drop Table Table_1;

[canmarroig]

Thanks Thiago, but FLAG_EVENT_ID, is the flag i've to create, joining the two tables 🙂

[Thiago_Justen_]

So the script should be like this:

able_1:
LOAD
id,
Timestamp(TimeStamp#(DATE_from,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as DATE_from,
Timestamp(TimeStamp#(DATE_TO,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as DATE_TO;
LOAD * Inline [
id, DATE_from, DATE_TO
1, "29/12/2018 11:00", "29/12/2018 12:00"
2, "29/12/2018 14:00", "29/12/2018 14:25"
3, "30/12/2018 14:00", "30/12/2018 19:00"
];

Table_2:
LOAD
Timestamp(TimeStamp#(EVENT,'DD/MM/YYYY hh:mm'),'DD/MM/YYYY hh:mm') as EVENT;
LOAD * Inline [
EVENT
"29/12/2018 14:00"
"29/12/2018 14:05"
"29/12/2018 14:10"
"29/12/2018 14:15"
"29/12/2018 14:20"
"29/12/2018 14:25"
"29/12/2018 14:30"
];

LEFT JOIN (Table_2)

IntervalMatch (EVENT)
LOAD
DATE_from,
DATE_TO
Resident Table_1;

Drop Table Table_1;

[canmarroig]

Thiago, I've tried but my last purpose is to add to Table 2 the column ID fromTable 1 but i still have not, maybe i've made a mistake? I don't need to drop table 1 since for this instance i've omitted but i've lot of infos that i need to keep in table 1