How do you join 2 tables while showing what's not in table 1?

[zaynaly]

This successfully shows a combined table with users that are in Table1 and Table2. However, I want to show all users in table1 that are NOT in table 2?

How can i do that?

| inputlookup table1.csv  
   | join type=inner userColumn  [ inputlookup table2.csv ]

[kamlesh_vaghela · Accepted solution]

@zaynaly

Can you please try this?

| inputlookup table1.csv | eval flag1="1" 
| join type=left userColumn 
    [ inputlookup table2.csv 
    | eval flag2="1" ] | where isnull(flag2) | table userColumn

Thanks

[HiroshiSatoh]

Try this!

| inputlookup table1.csv  where NOT [| inputlookup table2.csv|table  userColumn ]

OR

| inputlookup table1.csv  
    | join type=inner userColumn  [ inputlookup table2.csv|eval flag=1 ]
    |fillnull value=0 flag| where flag=0

[zaynaly]

Not working, neither of them 😕

first option gives all result of table2, and 2nd option errors out.