cancel
Showing results for 
Search instead for 
Did you mean: 

Neo4j Pivot table visualization

simsree9
Node

Hi ,

I have data i want to build graph visualization like excel pivots please refer below screenshot , please help anyone 

simsree9_0-1658993122042.png

 

 

11 REPLIES 11

glilienfield
Ninja
Ninja

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:

Screen Shot 2022-07-28 at 9.15.08 AM.png

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

 Screen Shot 2022-07-28 at 9.18.44 AM.png

A snippet of json to visualize it easier:

Screen Shot 2022-07-28 at 9.22.14 AM.png

Is this what you are looking for?

@glilienfield  ,

Thanks for the response i am getting below output says"There is no records .

simsree9_0-1659059603196.png

 

You need to adapt it to your data model.  I made up test data to illustrate its results.  I can help if you provide details of your data model or a script to generate test data. 

Hi @glilienfield ,

The Actual data looks like 

Region Location Category SubCategory
India Chandigarh SEZ SDBAWS Server Hosting in DMZ (Peri-Pheral DMZ Cloud) AWS
India Chandigarh SEZ SDBAWS Software Issues
India Bangalore_GHS Email Outlook Client Connectivity/Mail Delivery Issues
India Gurgaon-Uniworld Towers Remote Connectivity VPN Connectivity Issues
India InfyCity B(0AWS-AWS0) Laptop Issues
India InfyCity B(0AWS-AWS0) Email Outlook Client Connectivity/Mail Delivery Issues
India Pune Phase III (Ascendas) Local Connectivity Connectivity Issues
India Mohali ITCity Remote Connectivity VPN Connectivity Issues
India Gurgaon-Uniworld Towers Laptop Issues
India chennai-Mcity(BAWS-B4)&ETA Email Outlook Client Connectivity/Mail Delivery Issues
India Mysore - GEC Unified Communications and Collaboration AudioConference-Hosted
India Pune Phase II SDBAWSAWS Software Issues
India Trivandrum SEZ SDBAWS Email Outlook Client Connectivity/Mail Delivery Issues
India InfyCity B(44-45) Email Outlook Client Connectivity/Mail Delivery Issues
India Pune Phase II SDBAWS0 Laptop Hardware Issues - LENOVO
India Pune Phase II SDBAWS0 Laptop Issues
India InfyCity B(44-45) Email Outlook Client Connectivity/Mail Delivery Issues
India Bangalore_BRG SEZ Internet Access Issues
India Mysore - GEC Local Connectivity Connectivity Issues
India Pune Phase III (Ascendas) Laptop Issues
India Pune Phase III (Ascendas) Unified Communications and Collaboration Federation with clients and partners
India Bangalore_MNC Laptop Issues
India BBSR-SEZ Software Issues
India BBSR-SEZ Software Issues
India Pune Phase I Client Connectivity Support
India Mysore - GEC ETA VDI VirtualMachine
India Mysore - GEC ETA VDI VirtualMachine
India Pune Phase II SDB(AWS-4) Software Issues
India Mysore - GEC Laptop Issues
India BBSR-SEZ Remote Connectivity VPN Connectivity Issues
India Mohali ITCity Remote Connectivity VPN Connectivity Issues
Europe Germany - Stuttgart Software Issues
India Hyderabad STP (B0AWS-AWS0) Software Issues
North America Bellevue Software Issues
India Chennai.Shols-SEZ Laptop Issues
India Pune Phase II SDBAWSAWS Client Connectivity Support
India Pune Phase II SDBAWSAWS Local Connectivity Connectivity Issues
India Pune Phase III (Ascendas) Laptop Issues
India InfyCity B(AWSAWS-AWS8) Software Issues
India BBSR-STP Laptop Issues
India Nagpur SEZ Software Issues
India Pune Phase II SDB(5-7) Software Issues
India Pune Phase II SDB(AWS-4) Laptop Issues
India Pune Phase II SDB(AWS-4) Laptop Hardware Issues - HP
India Pune Phase I Laptop Issues
India Pune Phase I Remote Connectivity VPN Connectivity Issues
Not Assigned Not Assigned Internet Access Issues
India Jaipur - SEZ Laptop Issues
India Jaipur - SEZ Software Issues
India Hyderabad STP (B0AWS-AWS0) Desktop Hardware Issues - HP
India Hyderabad STP (B0AWS-AWS0) Software Issues
India Bangalore_GHS Laptop Issues
India Hyderabad SEZ (BAWS7-AWS8) Email Outlook Client Connectivity/Mail Delivery Issues
India Bangalore_EQX-EdgeVerve Software Issues
India InfyCity B(30-33) & B(49-50) Software Issues
India Mysore - GEC Laptop Issues
India Mysore - GEC Laptop Hardware Issues - HP
India Bangalore_MNC Local Connectivity Connectivity Issues
India Pune Phase II SDBAWSAWS Internet Access Issues
India Pune Phase II SDB(5-7) Software Issues
India Pune Phase II SDB(5-7) Laptop Issues
India Hyderabad SEZ (BAWS7-AWS8) Laptop Hardware Issues - HP
India InfyCity B(AWSAWS-AWS8) Internet Access Issues
India Pune Phase II SDB(AWS-4) Laptop Issues
Middle East Dubai Laptop Issues
India Mysore - GEC Laptop Issues
India Mysore - GEC Software Issues
India Bangalore_GHS Laptop Issues
India Hyderabad SEZ (BAWS7-AWS8) Laptop Issues
India Trivandrum SEZ SDB3 Laptop Issues
India Hyderabad STP (B0AWS-AWS0) Local Connectivity Connectivity Issues
India Chennai-Mcity(B5-B6) Laptop Issues
India Hyderabad SEZ (B06-AWS4) Mobility BYOD
India Hyderabad SEZ (B06-AWS4) Remote Connectivity VPN Connectivity Issues
India Pune Phase II SDB(5-7) Laptop Issues
India Bangalore_ECity SEZ Laptop Issues
India Bangalore_ECity SEZ Client Connectivity Support
India Bangalore-JPTower Laptop Hardware Issues - MAC
India Chandigarh SEZ SDBAWS Remote Connectivity VPN Connectivity Issues
India Pune Phase III (Ascendas) Software Issues
India Indore SEZ Email Outlook Client Connectivity/Mail Delivery Issues
India Mysore - GEC Laptop Issues
India chennai-Mcity(BAWS-B4)&ETA Client Connectivity Support
India Bangalore_EQX-EdgeVerve Software Issues
India Trivandrum SEZ SDB3 Software Issues
India Chennai-Mcity(B5-B6) Laptop Issues
India InfyCity B(34-43), Pyramid & Hostel Block Laptop Issues
India Pune Phase II SDB(AWS-4) Local Connectivity Connectivity Issues
India Bangalore_ECity SEZ Laptop Hardware Issues - LENOVO
India Bangalore_ECity SEZ Email Outlook Web Access (OWA) Connectivity
India Hyderabad STP (B0AWS-AWS0) Remote Connectivity VPN Connectivity Issues
India Hyderabad STP (B0AWS-AWS0) Email Outlook Client Connectivity/Mail Delivery Issues
India Chandigarh SEZ SDBAWS Software Issues
India Bangalore_GHS Laptop Issues
India Indore SEZ Software Issues
India Pune Phase I Laptop Issues
India chennai-Mcity(BAWS-B4)&ETA Software Issues
India BBSR-SEZ Software Issues
India Trivandrum SEZ SDB3 Remote Connectivity VPN Connectivity Issues
India InfyCity B(34-43), Pyramid & Hostel Block Laptop Issues
India Jaipur - SEZ Email Outlook Client Connectivity/Mail Delivery Issues
India Bangalore_ECity SEZ Software Issues
India Hyderabad STP (B0AWS-AWS0) Remote Connectivity VPN Connectivity Issues
India Hyderabad STP (B0AWS-AWS0) Email Outlook Client Connectivity/Mail Delivery Issues
India InfyCity B(0AWS-AWS0) Laptop Issues
India Mangalore SEZ Issues Laptop Issues
India Mangalore SEZ Issues Software Issues
India BBSR-SEZ Software Issues
India BBSR-SEZ Software Issues
India Jaipur - SEZ Laptop Issues
India Mysore - SEZ B(AWS7-AWS8) Software Issues
India Bangalore-JPTower Laptop Issues
India Pune Phase II SDB(5-7) Software Issues
North America Hartford Mobility BYOD
India Pune Phase II SDB(AWS-4) Software Issues
India Bangalore_GHS Laptop Issues
India Chandigarh SEZ SDBAWS Laptop Issues
India Hyderabad STP (B0AWS-AWS0) Laptop Issues
India Bangalore_BRG SEZ Client Connectivity Support
India InfyCity B(34-43), Pyramid & Hostel Block _OLA IT Services Client Connectivity
India Chennai-Mcity(B5-B6) Remote Connectivity VPN Connectivity Issues
India Indore SEZ Remote Connectivity VPN Connectivity Issues
India Trivandrum SEZ SDBAWS Laptop Issues
India Bangalore_BRG SEZ Laptop Issues
Europe Poland-Wroclaw Remote Connectivity VPN Connectivity Issues
India Bangalore_ECity SEZ Laptop Issues
India Chandigarh SEZ SDBAWS Laptop Hardware Issues - HP
North America Hartford Laptop Hardware Issues - HP
India Mysore - GEC Laptop Issues
India Mysore - GEC Laptop Issues
India Chennai-Mcity(B5-B6) Software Issues
India Chandigarh SEZ SDBAWS Software Issues
India Chennai-Mcity(B5-B6) MyCloud Virtual Machine Network Issues
India Chennai-Mcity(B5-B6) Email Outlook Client Connectivity/Mail Delivery Issues
India Chennai-Mcity(B5-B6) Software Issues
North America Toronto Email Outlook Client Connectivity/Mail Delivery Issues
India Pune Phase II SDB(5-7) Software Issues
India Bangalore_BRG SEZ Laptop Issues
India Mysore - GEC Email Outlook Client Connectivity/Mail Delivery Issues
India Bangalore_IIPMSTP Software Issues
India Hyderabad STP (BAWSAWS-AWS9) Email Outlook Web Access (OWA) Connectivity
India Bangalore_ECity SEZ Laptop Issues
India Bangalore_ECity SEZ Email Outlook Client Connectivity/Mail Delivery Issues
India Chandigarh SEZ SDBAWS Software Issues
North America Richardson Software Issues
Europe Sweden - Stockholm Remote Connectivity VPN Connectivity Issues
India Pune Phase II SDB(AWS-4) Client Connectivity Support
India Hyderabad STP (BAWSAWS-AWS9) Laptop Issues
India InfyCity B(34-43), Pyramid & Hostel Block Local Connectivity Connectivity Issues
APAC Tokyo Unified Communications and Collaboration Instant Messaging
North America Toronto Internet Access Issues
North America Toronto Email Outlook Client Connectivity/Mail Delivery Issues
India Trivandrum SEZ SDB3 Software Issues
India Pune Phase II SDB(5-7) Software Issues
India Pune Phase II SDB(5-7) Unified Communications and Collaboration Webcast
India Bangalore-JPTower Laptop Issues
India Trivandrum SEZ SDB3 Remote Connectivity VPN Connectivity Issues
India Pune Phase II SDBAWSAWS Client Connectivity Support
India Hyderabad STP (B0AWS-AWS0) Laptop Issues
India Hyderabad STP (B0AWS-AWS0) Software Issues
India Chandigarh SEZ SDBIssues Local Connectivity Connectivity Issues
India chennai-Mcity(BAWS-B4)&ETA Email Outlook Client Connectivity/Mail Delivery Issues
India InfyCity B(AWSAWS-AWS8) Laptop Hardware Issues - HP
India Pune Phase II SDB(AWS-4) Email Outlook Client Connectivity/Mail Delivery Issues
India Hyderabad SEZ (B06-AWS4) Client Connectivity Support
India BBSR-SEZ Software Issues
India chennai-Mcity(BAWS-B4)&ETA Laptop Issues
India Pune Phase II SDBAWS0 Wireless Connectivity Access Issues (Employee)

Hi @glilienfield ,

My data looks like how ever i couldnt build model please help me 

Region Location Category SubCategory
India Chandigarh SEZ SDBAWS Server Hosting in DMZ (Peri-Pheral DMZ Cloud) AWS
India Chandigarh SEZ SDBAWS Software Issues
India Bangalore_GHS Email Outlook Client Connectivity/Mail Delivery Issues
India Gurgaon-Uniworld Towers Remote Connectivity VPN Connectivity Issues
India InfyCity B(0AWS-AWS0) Laptop Issues
India InfyCity B(0AWS-AWS0) Email Outlook Client Connectivity/Mail Delivery Issues
India Pune Phase III (Ascendas) Local Connectivity Connectivity Issues
India Mohali ITCity Remote Connectivity VPN Connectivity Issues
India Gurgaon-Uniworld Towers Laptop Issues
India chennai-Mcity(BAWS-B4)&ETA Email Outlook Client Connectivity/Mail Delivery Issues
India Mysore - GEC Unified Communications and Collaboration AudioConference-Hosted
India Pune Phase II SDBAWSAWS Software Issues
India Trivandrum SEZ SDBAWS Email Outlook Client Connectivity/Mail Delivery Issues
India InfyCity B(44-45) Email Outlook Client Connectivity/Mail Delivery Issues

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. 

Screen Shot 2022-07-29 at 3.20.17 PM.png

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`

Screen Shot 2022-07-29 at 5.13.25 PM.png

 

HI @glilienfield ,

Thank you for quick help and its working if anything further will discuss with you

I just noticed I did not paste the final version. The query that tallies the issues by region, location, category, and subcategory does not correctly rollup the counts.  The following corrects that by using a 'sum' instead of a 'count' at each level except the first level. 

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, sum(totalBySubcategory) as totalByCategory, collect({subcategory: subcategory, count: totalBySubcategory}) as categoryStats
with region, location, sum(totalByCategory) as totalByLocation, collect({category: category, count: totalByCategory, stats: categoryStats}) as locationStats
with region, sum(totalByLocation) as totalByRegion, collect({location: location, count: totalByLocation, stats: locationStats}) as regionStats
return *

 

 

Hi @glilienfield ,

if it is in Graph or some visual it should be fine- does this possible 

simsree9
Node

Hi @glilienfield ,

Can we bring this following " Total By Subcategory" row as Graph or some visualization ?

simsree9_0-1659324019217.png

 

The information will need to be represented as nodes in order to visualize in desktop. I guess you could create 'Count' nodes that contain the count and are linked to their corresponding 'Category' and 'Subcategory'.  My issue with such an approach is that the counts will need to be maintained in realtime as you add issues to ensure they are current, or you understand they are just a snapshot at one point in time and they are used strictly for visualizing that one snapshot. 

My suggestion is to visualize it in excel using its pivot table and charting capabilities. You can slice the data as you want. For this, I would expand the query to give you the total by region, location, category, and subcategory, so you can group the data in any way you want to see the breakdown. The query to get this data is the following:

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)
return r.name as region, l.name as location, c.name as category, s.name as subcategory, count(*) as totalIssues

 

Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

On November 16 and 17 for 24 hours across all timezones, you’ll learn about best practices for beginners and experts alike.