How do I add two rows to create a new row in my table?

[MachaMilkshake]

I have created a table with a series of outer joins.

I now have column 'Category', and another 6, call them A to F.

in Category, I have values N1 and N2, and I would like to create a new row with Category=N3, and values for A to F equals to the sum of those for N1 and N2. I've tried every possible thing I could find but couldn't get it to work, any help is appreciated, thanks! 

Current code looks something like this:

... queries & joins...

| table "Category" "A" ... "F"

[MachaMilkshake]

Fixed it myself with appendpipe

[tscroggins]

Hi @MachaMilkshake,

The addcoltotals command should do exactly what you need:

| addcoltotals labelfield=Category label=N3

[MachaMilkshake]

Hi thanks for the reply, I have more rows other than N1 and N2

[tscroggins]

You can put whatever you want in the label argument. It sets the value of the field specified by labelfield in the totals row:

| addcoltotals labelfield=Category label="Total"

| addcoltotals labelfield=Category label="SUM[n1..nN]"

| addcoltotals labelfield=Category label="Life, the Universe and Everything"

[MachaMilkshake]

Sorry I'm still not quite getting it, this is what I would like to achieve:

Category	A	B	C	D	E	F
N1	1	2	4	2	4	1
N2	0	5	4	3	5	7
M1	1	0	1	0	4	3
M2	1	1	3	5	0	1
U1	0	4	6	5	4	3

I would like to create an additional row that is the sum of N1 and N2, and append it to the table above.

N3

1

7

8

5

9

8

[tscroggins · Accepted solution]

Ah, that detail wasn't clear from the original message. You can use the appendpipe command to stream the base search results through a subsearch and then append the subsearch results to base search results:

| makeresults format=csv data="Category,A,B,C,D,E,F
N1,1,2,4,2,4,1
N2,0,5,4,3,5,7
M1,1,0,1,0,4,3
M2,1,1,3,5,0,1
U1,0,4,6,5,4,3" 
| table Category *
| appendpipe 
    [| search Category=N* 
    | stats sum(*) as * 
    | eval Category="N3" ]

=>

Category	A	B	C	D	E	F
N1	1	2	4	2	4	1
N2	0	5	4	3	5	7
M1	1	0	1	0	4	3
M2	1	1	3	5	0	1
U1	0	4	6	5	4	3
N3	1	7	8	5	9	8

[tscroggins]

--if your base search includes N1, N2, N3, ... Nn, you can add additional logic to generate the N-value dynamically:

| appendpipe 
    [| search Category=N* 
    | stats count sum(*) as * 
    | eval Category="N".tostring(count+1)
    | fields - count ]

Or if you want to sub all Category values:

| appendpipe 
    [| rex field=Category "(?<Category>[^\d]+)"
    | stats count sum(*) as * by Category
    | eval Category=Category.tostring(count+1)
    | fields - count ]

=>

Category	A	B	C	D	E	F
N1	1	2	4	2	4	1
N2	0	5	4	3	5	7
M1	1	0	1	0	4	3
M2	1	1	3	5	0	1
U1	0	4	6	5	4	3
M3	2	1	4	5	4	4
N3	1	7	8	5	9	8
U2	0	4	6	5	4	3

But note that you'll need to add custom sorting logic if you want something other than the default sort order.

[MachaMilkshake]

I worked it out just now too with appendpipe, thanks a lot for your detailed response. I should've marked your response as the solution.. Thanks again!