Showing rows and columns when they do not exist in the event data

[aelliott]

I have a spreadsheet with a list of locations.
I have a list of Categories.
I have events of incidents with an office location on them that is the same as the office location on the spreadsheet,and categories that also are the same as the spreadsheet.

Data:
Incident1 Office A Category A
Incident2 Office A Category A
Incident3 Office B Category B
Incident4 Office D Category B

Spreadsheet Example:
Office A
Office B
Office C
Office D

I'm looking to make a search that has this as the results:
Category A Category B Category C
Office A 2 0 0
Office B 0 1 0
Office C 0 0 0
Office D 0 1 0

I was thinking of importing the list of Categories and locations as events and going from there, but the real issue here is how do I get Office C to show up as well as Category C in this scenario, when there are no incidents for them.

Here is what I have currently, but it does not show Category C or Office C as they do not exist in the data.
index=myIndex | stats count by Office Category | xyseries Office Category count | fillnull value=0

[aelliott · Accepted solution]

I've figured this out , it was a lot of fun 🙂

|inputlookup Offices.csv | join type=left max=0 [|inputlookup Categories.csv ] | join type=left OFFICE_DESC, Category [search index=incident_logs_dev | stats count by OFFICE_DESC Category] | fillnull value=0 | xyseries OFFICE_DESC Category count