I have a dataset 1 like:
VID
A212
A213
B151
B153
Dataset 2 like:
QID Solution
2145 text contains A212
2155 text contains B151
2157 text contains A212
2855 text contains B151
Wanted output like:
VID QID Solution
-> for all matching cases
I tried the following search for a single item search and it is working fine
|from inputlookup:"dataset1" |where like(vid,"%A212%")|stats values(vid) as vid
|map [|from inputlookup:"dataset2" |where like(Solution,"%".$vid$."%") |Table QID,Solution]
But when the main search returns multiple values, it is not working - shows no results.
|from inputlookup:"dataset1" |where like(vid,"%A%")|stats values(vid) as vid
|map [|from inputlookup:"dataset2" |where like(Solution,"%".$vid$."%") |Table QID,Solution]
What am i doing wrong here ?
The map
command will, by default, only take the first ten values. If you'd like it to look at more than ten, you need to use the maxsearches
flag, like: | map maxsearches=100 [...]
. You can also use maxsearches=0
to have no limit. However, I'd avoid using map
for this, because you can restructure this to not use map
at all, and doing so will launch a new search for every result from the base search - a very inefficient way to find what you're looking for. See if this restructured query will give you what you're looking for:
| inputlookup dataset2 where
[ | inputlookup dataset1 where vid="*A212*"
| eval vid="*".vid."*"
| stats values(vid) AS vid
| format ]
This restructured query takes the values from dataset1, reformats them with wildcards, and uses them as a search filter for dataset2.
The map
command will, by default, only take the first ten values. If you'd like it to look at more than ten, you need to use the maxsearches
flag, like: | map maxsearches=100 [...]
. You can also use maxsearches=0
to have no limit. However, I'd avoid using map
for this, because you can restructure this to not use map
at all, and doing so will launch a new search for every result from the base search - a very inefficient way to find what you're looking for. See if this restructured query will give you what you're looking for:
| inputlookup dataset2 where
[ | inputlookup dataset1 where vid="*A212*"
| eval vid="*".vid."*"
| stats values(vid) AS vid
| format ]
This restructured query takes the values from dataset1, reformats them with wildcards, and uses them as a search filter for dataset2.