How to join two with datasets with a common field using appendcol ?

[zacksoft_wf]

I have a lookup | inputlookup citizen_data , it has fields ID, Name, State.
I have another sourcetype | index=bayseian souretype=herc , that has fields citizen_ID, mobile, email.

My target is to enrich the "citizen_data" lookup with additional columns so that, while doing  |inputlookup citizen_data, I should see ID, Name, State, Mobile, Email.

NOTE :  ID field in the lookup is same as citizen_ID field in the sourcetype and I wanted the appendcol to link properly as per the matching ID data.

But when I executed my query with appendcol, it does append new columns/fields in the lookup, but it doesn't link or match them comparing  the common field i.e.  the ID. It just randomly appends the new coloumns. The rows contain incorrect data.

Any suggestion  how to append properly by  the common field (ID/citizen_ID)?

[ITWhisperer]

Either use join or append (rows of events) and then gather using stats by ID. In both case you will need to rename the common field to be the same name

[zacksoft_wf]

I did rename, but it didn't append based on the common field it just appended randomly Here is the query ================================ | inputlookup citizen_data | eval ID1 = ID | rename ID1 as citizen_ID | stats values(name) as name, values(state) as state  by citizen_ID | appendcols [ search index=bayseian souretype=herc | stats values(mobile) as mobile, values(email) as email by citizen_ID ] | table ID, name, state, mobile, email ============================================

[ITWhisperer · Accepted solution]

I said append not appendcols!

| inputlookup citizen_data 
| eval ID1 = ID 
| rename ID1 as citizen_ID 
| stats values(name) as name, values(state) as state  by citizen_ID 
| append 
  [ search index=bayseian souretype=herc 
  | stats values(mobile) as mobile, values(email) as email by citizen_ID ]
| stats values(name) as name, values(state) as state values(mobile) as mobile, values(email) as email  by citizen_ID