How to get sum stats from pair of values

[maellebrown]

Hi!
I am looking for help for, I think, a simple statistic but I can't figure out how to do this simply.
Here's an example of my data :

1. Customer1=A, Customer2=B
2. Customer1=A, Customer2=C
3. Customer1=B, Customer2=A

and I want spunk to count the number of event by pair of customer, like :

Pair=AB, count=2
Pair=AC, count=1

I'm sure spunk can do that really easily but all I can do is that and it's pretty ugly and duplicates the result :

eval pair1=Customer1. " / ". Customer2
eval pair2=Customer2. " / ". Customer1
eval pair=mvappend(pair1, pair2)
stats count by pair

Please help!

[DalJeanis]

Try

Your search
| rename COMMENT as "stats the first time to get ordered pairs"
| stats count as count1 by Customer1 Customer2

| rename COMMENT as "sort customer names into order and then combine prior stats"
| eval CustomerA=if(Customer1<=Customer2,Customer1,Customer2)
| eval CustomerB=if(Customer1<=Customer2,Customer2,Customer1)
| stats sum(count1) as count by CustomerA CustomerB

gives you

CustomerA CustomerB count
A         B         2
A         C         1

[maellebrown]

Yes thank you !

[somesoni2]

Try like this

your current search giving fields Customer1 and Customer2
| eval CustomerPair=mvsort(split("/".Customer1."##/".Customer2,"##"))
| nomv CustomerPair
| stats count by CustomerPair
| eval CustomerPair=replace(CustomerPair,"^\/(.+)","\1")

[maellebrown]

Thanks for the answer ! 🙂

[jacobpevans · Accepted solution]

Greetings @maellebrown,

Please try this run-anywhere example:

           | makeresults | eval Customer1="A", Customer2="B"
| append [ | makeresults | eval Customer1="A", Customer2="C" ]
| append [ | makeresults | eval Customer1="B", Customer2="A" ]
| eval Customer1_sort=if(Customer1<Customer2,Customer1,Customer2),
       Customer2_sort=if(Customer1<Customer2,Customer2,Customer1)
| eval CustomerPair  = Customer1_sort . " / " . Customer2_sort
| stats count by CustomerPair

Output:

CustomerPair    count
A / B            2
A / C            1

[maellebrown]

It works !!! Thanks a lot !! I knew it was easy but sometimes I'm lost with all that commands !! Thank you !

[jacobpevans]

Glad to hear it - you're welcome! Thank you for marking the answer for us and anyone who comes across this in the future.