Determining if one value appears in a column when multiple records exist

Question & Answer
1

If I have records such as:

DeviceID Device Result Timestamp
1A:23:56 Success 02-23-2023
1A:23:56 Failure 02-24-2023
2B:57:83 Failure 02-01-2023
2B:57:B3 Failure 02-02-2023
2B:57:B3 Failure 02-03-2023
1C:19:58 Success 02-06-2023
8C:54:C6 Success 02-15-2023
8C:54:C6 Success 02-15-2023
1B:34:9W Failed 02-27-2023
1B:34:9W Success 02-27-2023

I am looking for help in determining how to identify all the devices which during the time frame of the search did not have a value of Success appear once. In the above example, I would want to see 2B:57:B3 as identified as never succeeded. The others did succeed at one point. Ultimately, I need to be able to total the number of devices which never succeeded, devices which failed first and then succeeded and of course devices which succeeded and then failed.

I am most stuck on determining based upon the limited data that the one device in the three tries never succeeded.

Suggestions? Countif, variations of ifelse, etc did not work since I need to consider all records not just the one row.

Thanks!

2

Can you do a countOver with an ifelse.

countOver(ifelse({result}=‘Success’,{DeviceId},NULL),[{DeviceId}],PRE_AGG)

This should give you a count per device ID of how many success there are.

3

hi Max, with your suggestion I figured it out. Thank you.

I created a calculated field called hard-failure and used: countOver(ifelse({result}=‘success’,{DeviceId}, NULL),[{DeviceId}], PRE_AGG) as you suggested. It returned a number value which I then created another calculated field and used:

ifelse({hard-failure}=0,‘y’,‘n’)

I was then able to determine the number of times devices had a value of 0 to determine how many devices never booted up successfully.

Thanks again.

1 Like