Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 🙂
Have you already tried Interval Match function?
By using Interval match function you can achieve this scenario.
IntervalMatch:
IntervalMatch (EVENT) LOAD DATE from, DATE TO Resident Table 1;
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;
Thanks Thiago, but FLAG_EVENT_ID, is the flag i've to create, joining the two tables 🙂
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;
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