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
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)
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)
Thanks. It worked.