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.
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
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
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
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