Get the difference of 2 columns after Grouping

[GadgetGeek]

After grouping to display a chart, where there are only 2 values produced, how do I calculate the difference between the 2 columns?

e.g. Sample search :

<search terms> | rex "Sent\s+(?<ProductType>[a-zA-Z]*)" | rex "Product:\s+(?<ProductId>[a-zA-Z0-9]{5}-[a-zA-Z0-9]{8})" | eval ProductType=if(ProductType="Apple", "Fruit", "Veg")  | chart count(ProductType) as Total by ProductId, ProductType

Giving:

ProductId                Fruit      Veg
12345-12345678            3          2

Where I want:

ProductId                Fruit      Veg  Diff
12345-12345678            3          2    1

[somesoni2 · Accepted solution]

If the ProductType is static, then the field name after chart will also be fixed, you can just use an eval to get the difference.

<search terms> | rex "Sent\s+(?<ProductType>[a-zA-Z]*)" | rex "Product:\s+(?<ProductId>[a-zA-Z0-9]{5}-[a-zA-Z0-9]{8})" | eval ProductType=if(ProductType="Apple", "Fruit", "Veg")  | chart count(ProductType) as Total by ProductId, ProductType | eval Diff=abs(Fruit-Veg)

[GadgetGeek]

Many thanks!!

[GadgetGeek]

It works - how does it work though, as "Fruit" and "Veg" are string values for 'ProductType'? It's kind of like:

Diff=abs(ProductType="Fruit", ProductType="Veg)..

[somesoni2]

After your chart command, the values of Product Type becomes field names. So you'll have actual field names like 'Fruit' and 'Veg', that what's been used for calculation.