How edit my search so that appendcols command appends results correctly?

[cchange]

Hi,

I'm trying to append the results from two tables. I used appendcols with override option. But results showing different in final output.

I used following query

Search query 1 | appendcols override=true [Search query2]

First Search Results

Team FM    
A         35    
B         81    
C          30    

Second sub Search Results

Team  SM
D       40
E       620
A   180
B        59
C           53

Currently results are showing

Team FM    SM
D   35  40
E   81  620
A   30  180
B   -      59
C   -    53

Results should show in below format. I'm not sure if this is a bug with appendcols option. Any inputs or help is appreciated.

Team FM    SM
D    -  40
E   -   620
A   35   180
B   81  59
C   30   53

thanks

[somesoni2 · Accepted solution]

The appendcols appends the result of two searches, row/result by result (row 1 of search 1 with row 1 of search 2 and so on). The override field defines if same field name is present in both the results, which value to keep (keep from search 1 or overwrite from search 2). I see that results from your both the searches doesn't have same row count and they are not sorted either, making appendcols not the ideal command for your need. What you need is to merge both results and keep values from second search, then you should use combination of append and stats like this.

To keep results from search 1

Search query 1 | append [Search query2] | stats list(FM) as FM list(SM) as SM by Team | eval FM=mvindex(FM,0)  | eval SM=mvindex(SM,0)

To keep results from search 2

Search query 1 | append [Search query2] | stats list(FM) as FM list(SM) as SM by Team | eval FM=mvindex(FM,-1)  | eval SM=mvindex(SM,-1)

[cchange]

Thanks. It worked.