How do I simulate doing a left join or outer join using the "lookup" command?

[bcronrath]

Issue I am running into right now is I have a result set that I want to pull in threshold values that reside in a lookup CSV file. I am looking up based on a cluster title field, but ideally, what I want to happen is for every single cluster title in this lookup file to produce a row, even if there were no results in my actual search for that cluster. Is there a way I can do this?

[somesoni2 · Accepted solution]

Try like this (assuming your current search gives a results set with unique values of field cluster_title)

your current search giving result with fields cluster_title and others
| append [| inputlookup yourlookup.csv | table cluster_title ]
| stats values(*) as * by cluster_title

[bcronrath]

works perfectly thank you so much!

[cmerriman]

you can try to use ...|appendpipe [stats count by clusterTitle |join max=0 clusterTitle [|inputlookup clusterTable.csv]|fields - count]|stats values(*) by clusterTitle

or something along those lines. Without knowing your actual syntax/data, that's the best I got.