Set attribute for all elements having a certain ID when 2 values are availabe witin the elements having this id

[lukas1]

Hi everyone,

I try to set an attribute to true for all elements having a certain ID, when 2 defined activities are available for that certain ID.

In my optinion the corresponding SQL query would be:

  Update t set isvalid = true where id in (select id from t group by id having activity = 'a' and activity = 'b')

A result might look like:

id	activitiy	istrue
001	a	true
001	b	true
001	c	true
002	a	false
002	c	false
002	d	false
003	a	true
003	b	true

Is there an option to execute this in SPL?

Thanks,

Lukas

[ITWhisperer · Accepted solution]

| eventstats values(activity) as activities by id
| eval istrue=if(isnotnull(mvfind(activities,"a")) and isnotnull(mvfind(activities,"b")),"true","false")

[lukas1]

Thank you @ITWhisperer !

Working now 😀