Compare 2 CSV files

[nomarja1]

I have two CSV files. One files has the name of the accounts and servers where the accounts are added.

The second CSV file I have a lookup breaking down the groups members.

The field name is in common with both CSV files.

e.g:  Accounts01.CSV

Class	Domain	Hostname	Name
User	robotic	ROB-SVR01	Administrator
Group	robotic	ROB-SVR01	Advanced_users_IT
Group	robotic	ROB-SVR01	Advanced_users_HR

e.g: GroupMembers.CSV

Name	member
Advanced_users_IT	user_IT_01 user_IT_02 user_IT_03
Advanced_users_HR	user_HR_01 user_HR_02 user_IT_01

Is there any way to combine both files to match the names and adding a new column showing the members, so the result can be like this?

Class	Domain	Hostname	Name	Members
User	robotic	ROB-SVR01	Administrator	User Account
Group	robotic	ROB-SVR01	Advanced_users_IT	user_IT_01 user_IT_02 user_IT_03
Group	robotic	ROB-SVR01	Advanced_users_HR	user_HR_01 user_HR_02 user_IT_01

[ITWhisperer]

| inputlookup Accounts01.CSV
| append
  [| inputlookup GroupMembers.CSV]
| stats values(member) as Members by Class Domain Hostname Name
| fillnull value="User Account" Members

[nomarja1]

@ITWhisperer  Thanks for replying, but unfortunately, that didn't work, it is showing the member fields "User Account"  where it should show the member of the group

[ITWhisperer]

Your example had "User Account" in that field - what value do you want in the field and where does it come from?

[nomarja1]

I have 2 CSV in the same format as I described.

I want to fill the add the member field displaying the members from the CSV  GroupMembers.

I managed to get that done, using JOIN type=left max=0, which is bringing the result I want. I know is not the best but I am going to use that for now.

[ITWhisperer · Accepted solution]

Sorry, too many fields in the by clause - try it this way

| makeresults
| eval _raw="Class	Domain	Hostname	Name
User	robotic	ROB-SVR01	Administrator
Group	robotic	ROB-SVR01	Advanced_users_IT
Group	robotic	ROB-SVR01	Advanced_users_HR"
| multikv forceheader=1
| table Class Domain Hostname Name
| append
    [| makeresults
    | eval _raw="Name	member
Advanced_users_IT	user_IT_01,user_IT_02,user_IT_03
Advanced_users_HR	user_HR_01,user_HR_02,user_IT_01"
    | multikv forceheader=1
    | eval member=split(member,",")
    | table Name member]



| stats values(*) as * values(member) as Members by Name
| fillnull value="User Account" Members

Not sure if you need the fillnull or something else if the Name has no entry in the GroupMembers.csv

[nomarja1]

That's perfect @ITWhisperer , I updated my search with your solution and it worked like a charm. The join command was working but made my search a bit slow.

I appreciate your help. thanks again.