Subtracting value if another field value matches

[raby1996]

Hi all,

Im running two searches one returns a number called "difference" and a field called "code2", the other searches is my main search which contains other various fields, however the ones that matter for this particular query are the "sum" and "code1" fields. What I'm trying to do is run a command so that when the fields "code1" and "code2" match, then the field value "difference" is subtracted from the field value "sum" giving me the final output of "final_number". Is this possible? It would like something like this-

First Search-

code1     sum 
1.1      100       
1.2      150
1.3      300

Second Search-

code2     difference
1.1           20
1.2           25
1.3           30

Desired Search Results-

code      final_number
1.1            130
1.2            125
1.3            270 

Thank you.

[somesoni2 · Accepted solution]

Give this a try

Your first search | table code1 sum | rename code1 as code
| append [your second search | table code2 difference | rename code2 as code]
| stats values(*) as * by code | eval final_number=abs(sum-difference)
| table code final_number

[jkat54]

1st search ...
| table code1, sum 
| append [ 
  2nd search ....
  | table code2, difference
] 
| eval code_match=if(match(code1,code2),1,0)
| eval diff=if(code_match=1,sum-difference,sum)
| table code1, code2, diff, sum

[javiergn]

I think your first example is wrong and it should return 80 for code 1.1, but in any case:

| yoursearch1 OR yoursearch2
| eval code = coalesce(code1,code2)
| eval values = coalesce(sum,-difference)
| stats sum(values) as final_number by code