How can I filter out the data by using the conditions on multiple fields?

Hangxing_Zhou

I have a huge data volume table, I need work out the total count number by a pattern.

Sample table fields and records:

Unit_Name Message_Name Time_Of_Message(YY/MM/DD/HH/MM)

Unit_A Message_B 2020-12-01-1:00

Unit_A Message_C 2020-12-01-1:10

Unit_A Message_B 2020-12-01-1:20

Unit_A Message_C 2020-12-01-1:30

Unit_A Message_B 2020-12-01-1:00

Unit_B Message_B 2020-12-01-1:20

Unit_B Message_A 2020-12-01-1:22

Unit_B Message_B 2020-12-01-1:25

Unit_B Message_B 2020-12-05-2:20

Unit_B Message_A 2020-12-05-2:20

Unit_B Message_B 2020-12-05-2:21

...

My requirements as below:

1. Remove All the records of the Unit_Name if the Unit_Name does not include Message_A(I have sorted data by Unit_Name and Time_of_Message)

For Example, remove all records for Unit_A as above

2. Count the total number and group by Unit_Name for all "Time_of_Message" of "Message_B" is bigger than "Time_of_Message" of "Message_A", and within 5mins

For example, Unit_B: 2

I tried some formulas, but seems doesn't work.

Thanks!

Data Investigation

Accepted answers

Qiu

@Hangxing_Zhou
I think I get your intention here, hopefully.

0119-Hangxing_Zhou.yxmd

All comments

BretCarr

I read your request at least 5 times and I'm still not sure what you are wanting to do. Can you show how the shown data (all of it) would be reformatted if everything you want to happen comes true?

Thanks!

Hangxing_Zhou

The data set grouped by "Unit_Name" - events happened on the unit separately by month, the requirements:

If no specific event(Message_A) happened on the unit in the month, I need remove all the records of the Unit from the table.

If the specific event(Mesage_A) happened on with the unit, then I need combine another event(Message B）by the "time_field" (diff time <5mins), message B happened later on message A. I need count the total number of this pattern for each unit by Month.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703