topic Re: Expression with two filters in QlikView

Expression with two filters

Mon, 09 Mar 2015 15:54:40 GMT

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.

Re: Expression with two filters

Anonymous — Mon, 09 Mar 2015 16:18:55 GMT

Jessica,

For instance, if you want to count Heartbeats:

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

Please share a qvw to further work on this.

Marc.

Re: Expression with two filters

Roop — Mon, 09 Mar 2015 16:23:13 GMT

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

Re: Expression with two filters

Mon, 09 Mar 2015 16:42:45 GMT

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.

Re: Expression with two filters

Anonymous — Mon, 09 Mar 2015 16:46:58 GMT

The try:

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

Marc.

Re: Expression with two filters

Mon, 09 Mar 2015 17:25:46 GMT

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.

Re: Expression with two filters

Anonymous — Mon, 09 Mar 2015 17:33:12 GMT

ok, i understand now. i would try:

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

Hope it helps.

Marc.