Join results from two lookups

[shaquibk]

My requirement is something like this:

Lookup 1 looks like this

Name | Avg_Count
A          | 3
B          |  7
D          | 8
F           | 5

Lookup 2 looks like this:

Name | Current_Count
A          | 2
C          | 4
D          | 6

In the search, I input both these lookups and want results like this:
Name | Avg_Count | Current_count
A          | 3                     | 2
B          | 7                     | 0/null (0 preferred)
D          | 8                     | 6
F          | 5                      | 0
C         | 0                      | 4

I have tried join/append/appendcols but all these have their limitations and won't give the intended results. Also looked at many solutions from community but couldn't find one.

Thanks in advance!

Shaquib

[shaquibk]

Thanks @ITWhisperer and  @gcusello 

Both the solutions were accurate and worked perfectly. Appreciate the prompt response too.

Thanks and Regards,

Shaquib

[gcusello]

Hi @shaquibk,

with append command you should have the results you need:

| inputlookup lookup1
| append [ | inputlookup lookup1 | fields Name Current_Count ]
| stats values(Avg_Count) AS Avg_Count values(Current_Count) AS Current_Count BY Name

If you have more numbers for Avg_Count or Current_Count, instead values use another option in the stats command (e.g. max or sum).

Ciao.

Giuseppe

[ITWhisperer · Accepted solution]

| inputlookup lookup1
| append [| inputlookup lookup2]
| stats values(*) as * by Name
| fillnull value=0