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?
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 ,
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
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)
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
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
Outlook Client Connectivity/Mail Delivery Issues
India
InfyCity B(44-45)
Outlook Client Connectivity/Mail Delivery Issues
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
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)
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
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
Outlook Client Connectivity/Mail Delivery Issues
India
InfyCity B(44-45)
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)
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)
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
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
Outlook Web Access (OWA) Connectivity
India
Hyderabad STP (B0AWS-AWS0)
Remote Connectivity
VPN Connectivity Issues
India
Hyderabad STP (B0AWS-AWS0)
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
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)
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)
Outlook Client Connectivity/Mail Delivery Issues
India
Chennai-Mcity(B5-B6)
Software
Issues
North America
Toronto
Outlook Client Connectivity/Mail Delivery Issues
India
Pune Phase II SDB(5-7)
Software
Issues
India
Bangalore_BRG SEZ
Laptop
Issues
India
Mysore - GEC
Outlook Client Connectivity/Mail Delivery Issues
India
Bangalore_IIPMSTP
Software
Issues
India
Hyderabad STP (BAWSAWS-AWS9)
Outlook Web Access (OWA) Connectivity
India
Bangalore_ECity SEZ
Laptop
Issues
India
Bangalore_ECity SEZ
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
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
Outlook Client Connectivity/Mail Delivery Issues
India
InfyCity B(AWSAWS-AWS8)
Laptop
Hardware Issues - HP
India
Pune Phase II SDB(AWS-4)
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)
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 ,
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
Hi @glilienfield ,
Can we bring this following " Total By Subcategory" row as Graph or some visualization ?
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