How do i display only events that aren't "backed out"?

[gregbo]

I have a table of fields with items that are either a Credit or Debit There can be multiples of the same item. Also, an item can be "backed out" by making it a Debit. Here's a sample:

So, "a" was credited twice and debited once. I'd like the table to show just one "a" (along with the other rows). Any idea how i can do this?

[woodcock · Accepted solution]

Like this:

|makeresults | eval raw="1,a,C::2,j,C::3,q,C::4,a,C::5,l,C::6,a,D::7,m,C::8,r,C"
| makemv delim="::" raw
| mvexpand raw
| rename raw AS _raw
| rex "^(?<line>[^,]+),(?<item>[^,]+),(?<CorD>.*)$"

| rename COMMENT AS "Everything above generates sample events; everything below is your solution"

| stats count(eval(CorD="C")) AS Credits count(eval(CorD="D")) AS Debits BY item
| eval CorD = Credits - Debits

[gregbo]

That worked like a charm! Thanks Woodcock!

[niketn]

@gregbo, you will have to add more details for Credit and Debit. For example if a is credited three times and Debited only once, will you have two rows for a?

Based on the current question if you want to display only one row per item, you can use dedup

<Your Base Search>
| dedup item
| table line item

[somesoni2]

You probably have to run some stats/dedup command to collapse duplicate rows. The command/function to use will depend upon what value of "C/D" you want to show.

If you want to show all values of C/D for a,

your current search giving field line item C/D | stats values("C/D") as "C/D" max(line) as line by item

If you just want to show the first/last value for C/D for a

your current search giving field line item C/D | stats first("C/D") as "C/D" first(line) as line by item