Expression with two filters

[#1]

I am looking to create an expression that does something like this:

Count distinct Heartbeat on devices that have also produced CRASH during same timeperiod.

I am not understanding how to count it since the crash and the heartbeat come in different records. My table includes the following columns:

Time/Date

EventID

Heartbeat

Crash

DeviceID

My expressions can be:

= Count( {<LogTime=$::LogTime,HeartbeatType={500}>} DISTINCT EventID )   <-- Just count heartbeats

= Count( {<LogTime=$::LogTime,Crash={Fatal}>} DISTINCT DeviceID )            <-- Just count devices that generate crash

But, I want to put those two together to only count the heartbeats on devices that have also generated crashes during that time period:

= Count( {<LogTime=$::LogTime,HeartbeatType={500},DeviceID{<LogTime=$::LogTime,Crash={Fatal}>}>} DISTINCT EventID )

Obviously this doesn't work! Appreciate the help.

[Anonymous]

Jessica,

For instance, if you want to count Heartbeats:

=Count( {<HeartbeatType={'500'}>} DISTINCT EventID )

Please share a qvw to further work on this.

Marc.

[Roop]

Count( {<LogTime=$::LogTime,HeartbeatType={500},Crash={Fatal}>}>} DISTINCT EventID ) should do it - You just don't need to repeat the log time again as you have already done that peice.

Hope it helps

Rupert

[#4]

The problem is that each record has either CRASH or HEARTBEAT values set. They do not have both! So, I need to count the events that have the heartbeat, but only where the device ID matches different events where the crash has occurred. Since they are separate events, I can't include them in the same (HeartbeatType={500},Crash={Fatal}) because the count is 0.

[Anonymous]

The try:

=Count( {<HeartbeatType={'500'}>+<Crash={'Fatal'}>} DISTINCT EventID )

Marc.

[#6]

Once again, this is not what I want because the eventID is not same for the event producing the crash and the event producing the heartbeat. I am trying to find where the device ID is the same. Here is the sample data, to hopefully make it more clear.

Sample Data:

LogTime    EventID      DeviceID       Heartbeat           Crash      

1:00pm       EventA        Device1         Yes                     -

2:00pm       EventB        Device2         Yes                     -

3:00pm       EventC        Device1         -                         Fatal

4:00pm       EventD        Device1         Yes                    -

In this example, I only want to count the 2 heartbeats from Device1 because Device1 is the only device that produced a crash. I need to make sure I don't count the heartbeat that was triggered by Device2 because it has not produced a crash.

Hope this helps.

[Anonymous · Accepted solution]

ok, i understand now. i would try:

=Count( {<HeartbeatType={'500'}, DeviceID=p({<Crash={'Fatal'}>} DeviceID)>} DISTINCT EventID )

Hope it helps.

Marc.