Combine 3 Fields with Same ID

[Hppjet]

I have 3 fields that will contain the same user IDs and I would like to merge them into 1. They each have a sum value they represent but the 3 fields are just a different role.

If the events look like
Day 1 Pounds=20,field1=A,field2=B,field3=C
Day 2 Pounds=50,field1=C,field2=A,field3=B
Day 3 Pounds =60,field1=B,field2=R,field3=A

Is it possible to combine the 3 so if field1 = A field2 = A field3 = A then the sum(Pounds)=130

[elliotproebstel · Accepted solution]

Are you looking to do this for only a single value at once? I'm trying to imagine taking the above events and producing a table that makes sense. For a single userID (such as "A"), I can envision a search that looks for all events where field1=A OR field2=A OR field3=A and then summing the pounds. No problem.

If you want the sum for every possible userID, I'd expect the solution to involve something like this:

your current search
| eval id=mvappend (field1, field2, field3)
| mvexpand id
| stats sum(pounds) AS total BY id

Given the data in you post, this would result in a table like this:

id  | total
A   | 130
B   | 130
C   | 70
R   | 60

Does that fit your specs?

[Hppjet]

Thank you!!! Yes, it worked perfectly. I wanted to be able to sum for every possible userID. This made my week.

[niketn]

@elliotproebstel, mvexpand id is not required.

Following is the run anywhere search based on sample data

| makeresults
| eval data="Day 1 Pounds=20,field1=A,field2=B,field3=C;Day 2 Pounds=50,field1=C,field2=A,field3=B;Day 3 Pounds =60,field1=B,field2=R,field3=A"
| makemv data delim=";" 
| mvexpand data
| rename data as _raw
| KV
| eval id=mvappend (field1, field2, field3)
| stats sum(Pounds) as Pounds by id

[elliotproebstel]

Good to know, thanks!

[skoelpin]

You're looking for the function coalesce. It will work like this

| eval new_field=coalesce(fieldA,fieldB,fieldC)

This will take fieldA, fieldB ,fieldC and combine their values into new_field

[elliotproebstel]

No, coalesce will assign to new_field the first non-null value it encounters in the list of fields supplied as arguments. So in the sample code, new_field would always contain the value of fieldA.