Add Rows from Lookup with no matches

[Sivrat]

I'm trying to use a lookup and some search results to display a table that includes both where something matched in the lookup, and where no matches were found in the lookup.

Sample search syntax would be:
[very long search, ends up in table with fieldA, fieldC]
| lookup MyLookup fieldA output fieldB
| chart values(fieldA) by fieldB, fieldC

This shows the values of field A, split by rows for the matching fieldB (pulled from lookup) and split by columns for fieldC.

I want to see all of the fieldB rows in the lookup added, even if there was no matching fieldA. Either blank or with a default value of 'None Found' or something in the chart results, since knowing something is 'missing' is important.

How can I do this? 

[scelikok · Accepted solution]

Hi @Sivrat,

Please try below;

[very long search, ends up in table with fieldA, fieldC]
| lookup MyLookup fieldA output fieldB
| inputlookup append=t MyLookup
| fillnull value="None Found" fieldB fieldC
| chart values(fieldA) by fieldB, fieldC

[Sivrat]

This worked after very slight adjustments.

[very long search, ends up in table with fieldA, fieldC]
| lookup MyLookup fieldA output fieldB
| inputlookup append=t MyLookup
| fillnull value="None" fieldC
| chart values(fieldA) by fieldB, fieldC
| fields - None

Since I actually didn't want all of the fieldA's from the original search, just all of them that matched in the lookup.
But the inputlookup and adding in fillnull allowed all of the fieldB to be present, and I just needed to remove the column that was the filler at the end. 

Thanks @scelikok 

[richgalloway]

Have you tried fillnull?

Sample search syntax would be:
[very long search, ends up in table with fieldA, fieldC]
| lookup MyLookup fieldA output fieldB
| fillnull value="None Found" fieldB
| chart values(fieldA) by fieldB, fieldC

[Sivrat]

Thanks for the response.

Fillnull covers cases where a row is present, but the column is blank. As long as one of the columns has value, it works since that means the row will be present

If there are rows in lookup that don't have any matches in the values returned by the search, then those won't get populated at all.

I'm now trying to surround the [very long search] with a join using combination of inputlookup and appendcols to try and build out the initial table, then join the rest with subsearch. But haven't quite gotten it working. Really hoping there is an easier way.