Head's Up! These forums are read-only. All users and content have migrated. Please join us at community.neo4j.com.
06-19-2020 02:19 AM
Hello,
I am trying to create a count table of products based on sales status. Sale status has two value - WON and LOST.
MATCH (sid:Sale)-[:have_outcome]->(ss:SaleStatus {name:'WON'})
MATCH (sid:Sale)-[:sales_of]->(p:Product)
WITH p.name as product, count(sid) as positive
MATCH (sid2:Sale)-[:have_outcome]->(ss2:SaleStatus {name:'LOST'})
MATCH (sid2:Sale)-[:sales_of]->(p2:Product)
RETURN product, positive, count(sid2) as negative
In the output I am not getting right value for 'negative' column
I would like to know how to solve it
Any small help or hint is appreciated and will surely help me a lot.
Thanks in advance.
Solved! Go to Solution.
06-19-2020 09:35 AM
Try below,
MATCH (p:Product) <-[:sales_of]- (sid:Sale)-[:have_outcome]->(ss:SaleStatus )
With p.name as product, ss.name as type, count(ss) as type_val
With product, collect([type,type_val]) as keypairlist
With product, apoc.map.fromPairs(keypairlist)as value
Return product, COALESCE(value.Won,0) as Won, COALESCE(value.Lost,0) as Lost
06-19-2020 07:06 AM
06-19-2020 08:21 AM
Hello Vivek,
thanks for your reply.
The example dataset :
The Graph model is as follows:
I am able to fetch the product wise count in two separate queries, but unable to combine and create a single table in the output.
Thanks in advance.
06-19-2020 09:35 AM
Try below,
MATCH (p:Product) <-[:sales_of]- (sid:Sale)-[:have_outcome]->(ss:SaleStatus )
With p.name as product, ss.name as type, count(ss) as type_val
With product, collect([type,type_val]) as keypairlist
With product, apoc.map.fromPairs(keypairlist)as value
Return product, COALESCE(value.Won,0) as Won, COALESCE(value.Lost,0) as Lost
06-19-2020 10:57 AM
Hello Vivek,
Thanks for the reply.
Appreciate your time.
I run the query you suggested, unfortunately its showing a error. I tried my part but cant find a solution i am adding error message below. Please suggest a solution if possible.
Unknown function 'apoc.map.fromPairs' (line 4, column 15 (offset: 210))
"With product, apoc.map.fromPairs(keypairlist) as value"
Thanks in advance
06-19-2020 11:02 AM
Have you installed APOC plugin on your database?
06-19-2020 11:47 AM
Hello Maxime,
Thanks for your help.
06-19-2020 11:05 AM
As suggested by Maxime install APOC
06-19-2020 11:44 AM
Hello Vivek,
I installed the APOC plugin now its working perfectly.
You provided a perfect code, even replaces the null value.
Thank you so much for your effort and help.
All the sessions of the conference are now available online