Combine values from mutliple rows

[magriii]

Goal is to subtract file counts of folders from sites MAIN and BACK.

Sample data

| makeresults
| eval f="MAIN-AAA", val="17313"
| append [| makeresults | eval f="BACK-AAA", val="17357"]
| append [| makeresults | eval f="MAIN-BBB", val="682"]
| append [| makeresults | eval f="BACK-BBB", val="682"]
| append [| makeresults | eval f="MAIN-CCC", val="38767"]
| append [| makeresults | eval f="BACK-CCC", val="38804"]
| eval site=substr(f,1,4) | eval folder=substr(f,6)

Output should be something like

folder,count MAIN,count BACK,difference
AAA,17313,17357,4
BBB,682,682,0
CCC,38804,38767,37

thanks

[ITWhisperer · Accepted solution]

| eval countMAIN=if(site="MAIN",val,NULL())
| eval countBACK=if(site="BACK",val,NULL())
| fields countMAIN, countBACK, folder
| stats values(*) as * by folder
| eval difference=abs(countMAIN-countBACK)