Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
canmarroig
Partner - Creator
Partner - Creator

How add a flag column for an event in a interval

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 🙂

Labels (2)
6 Replies
sunny_talwar

Have you already tried Interval Match function?

Interval Match

hari8088
Creator
Creator

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;

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
canmarroig
Partner - Creator
Partner - Creator
Author

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;

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
canmarroig
Partner - Creator
Partner - Creator
Author

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