topic Re: How add a flag column for an event in a interval in App Development

How add a flag column for an event in a interval

canmarroig — Sat, 16 Nov 2024 07:18:22 GMT

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 🙂

Re: How add a flag column for an event in a interval

sunny_talwar — Wed, 14 Nov 2018 12:39:29 GMT

Have you already tried Interval Match function?

Interval Match

Re: How add a flag column for an event in a interval

hari8088 — Wed, 14 Nov 2018 12:46:38 GMT

By using Interval match function you can achieve this scenario.

IntervalMatch:

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

Re: How add a flag column for an event in a interval

Thiago_Justen_ — Wed, 14 Nov 2018 13:03:55 GMT

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;

Re: How add a flag column for an event in a interval

canmarroig — Wed, 14 Nov 2018 13:14:34 GMT

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

Re: How add a flag column for an event in a interval

Thiago_Justen_ — Wed, 14 Nov 2018 13:16:59 GMT

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;

Re: How add a flag column for an event in a interval

canmarroig — Wed, 14 Nov 2018 13:49:03 GMT

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