topic Re: Map with inputlookup not working in Getting Data In

Map with inputlookup not working

nsraoch1975 — Wed, 24 Jan 2018 15:13:40 GMT

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 ?

Re: Map with inputlookup not working

elliotproebstel — Wed, 24 Jan 2018 18:04:26 GMT

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.