Hi ,
I have data i want to build graph visualization like excel pivots please refer below screenshot , please help anyone
Well, I can't output it in pivot table format, but I can generate the data organized in the same format.
Assuming a data model like below:
This query will tally the individual issues within a category and also the total number of issues per category:
match(n:Category)<-[]-(m:Issue)
with n.name as category, m.name as type, count(*) as cnt
return category, collect({type: type, issues: cnt}) as stats, sum(cnt) as total
A snippet of json to visualize it easier:
Is this what you are looking for?
I assumed each row in your data represents a single issue or incident you are tracking. With that, I assumed the following data model, where an issue is associated with a location and a subcategory is issue type.
I imported your data with the following script to generate this data model:
load csv with headers from "file:///IssueLog.csv" as row
create(i:Issue)
merge(r:Region{name: row.Region})
merge(l:Location{name: row.Location})
merge(c:Category{name: row.Category})
merge(s:Subcategory{name: row.SubCategory})
merge(s)-[:MEMBER_OF]->(c)
merge(l)-[:IN_REGION]->(r)
merge(i)-[:IN_LOCATION]->(l)
merge(i)-[:IS_TYPE]->(s)
The following script rolls up the count of issues by region, location, category, and subcategory:
match(i:Issue)
match(i)-[:IN_LOCATION]->(l:Location)
match(i)-[:IS_TYPE]->(s:Subcategory)
match(s)-[:MEMBER_OF]->(c:Category)
match(l)-[:IN_REGION]->(r:Region)
with r.name as region, l.name as location, c.name as category, s.name as subcategory, count(*) as totalBySubcategory
with region, location, category, count(*) as totalByCategory, collect({subcategory: subcategory, count: totalBySubcategory}) as categoryStats
with region, location, count(*) as totalByLocation, collect({category: category, count: totalByCategory, stats: categoryStats}) as locationStats
with region, count(*) as totalByRegion, collect({location: location, count: totalByLocation, stats: locationStats}) as regionStats
return *
The result is a row per region, with the regional total and an array of the region's stats totaling the number of issues by location, category, and subcategory.
If you don't need it broken down as much, the following tallies the incidents just by category and subcategory, ignoring region and location:
match(i:Issue)
match(i)-[:IS_TYPE]->(s:Subcategory)-[:MEMBER_OF]->(c:Category)
return c.name as Category, s.name as Subcategory, count(*) as `Total By Subcategory`
Hi @glilienfield ,
Can we bring this following " Total By Subcategory" row as Graph or some visualization ?
NODES 2022, Neo4j Online Education Summit
All the sessions of the conference are now available online
