How to change specific value of grouped values?

[lukas1]

Hi,

I have a lot of event data, where every instance can be idendified by a unique ID. Every instance contains several activities. Some activities occur not only once. For some this is okay, but for others I would like to add e.g. a "_2" at the end of the activity name for the second occurence of this activity.

As this should be performed only for the second activity within the instance and only for some activities within all, I was not sure if it is possible to transform the data with SPL in the way I need it to be.

Thanks for your support!

[gcusello]

Hi @lukas1,

you have to detail all the rules in an eval command,

If you could share some additional information, I could be more detailed.

Ciao.

Giuseppe

[lukas1]

Hi @gcusello ,

Thanks for your quick reply!

Here you can find a exaplary data set to illustrate my problem.

Currently I only have the field activity. For every caseID there are several activities (for caseID 1, there is 2x a, b, 2x c, d). For activity a it is totally okay to be there twice. But for activity c i want to differ the first and the second occurence within every case.

Hope this could clarify some things.

Lukas

[bowesmana · Accepted solution]

Possibly with that table, you could add

your search to produce the base table
| streamstats count by caseID activity
| eval "activity (to-be)"=if(activity="c" AND count>1, activity."_".count, activity)
| fields - count

what this basically does if create your activity to be column based on either activity, or if the count of activity 'c's is more than one, add suffix of _{count} where count is calculated with the streamstats.

[lukas1]

Thanks @bowesmana  for your answer!

It's working for me!

So when i would also like for activity z to behave like c, would it then be a good approach to proceed like:

| streamstats count by caseID activity

| eval "activity (to-be)"=if(activity="c" AND count>1, activity."_".count, activity)

| eval "activity (to-be)"=if(activity="z" AND count>1, activity."_".count, activity)

| fields - count

Otherwise it would mess up the count for c - would it?

Best,

Lukas

[bowesmana]

You can make it in a single eval statement

| eval "activity (to-be)"=if(count>1 AND (in(activity, "c","z" )), activity."_".count, activity)

so, if count>1 and activity is either c or z it will behave the same way

[lukas1]

Thank you @bowesmana ! It's working!

Best,

Lukas