cancel
Showing results for
Did you mean:

Head's Up! Site migration is underway. Phase 2: migrate recent content

## converting list to constant Node Clone

without success I tried to convert a list to a constant (please see attached)
I created the correct calculation

MATCH p=(n:Account{Account:'Acct 6100'})-[r:Driver]->(d:Department)
return  apoc.coll.sum(collect(r.Driver)) as driverSum
and retrieve total of 196 , but when I tried to use it for calculation to dived each number by the total with
MATCH p=(n:Account{Account:'Acct 6100'})-[r:Driver]->(d:Department)
return  d.Department as name,r.Driver as driver,apoc.coll.sum(collect(r.Driver)) as driverSum,n.Amount as alloc_amt
it retrieve the same r.Driver

the question is there a way to convert a list to a constant (196) or we have to use collect and the function for each?

1 ACCEPTED SOLUTION  Ninja

I am not sure what you are looking for, but here is an example of calculating the driver total by department and then dividing each by the total across all departments.

``````match (n:Account{Account:'Acct 6100'})-[r:Driver]->(d:Department)
with n, d.Department as dept, sum(r.Driver) as deptDriverTotal
with n, collect({dept: dept, total: deptDriverTotal}) as deptStats, sum(deptDriverTotal) as total
unwind deptStats as deptStat
return n.amount as amt, deptStat.dept as department, deptStat.total as deptTotal, 100 * (toFloat(deptStat.total) / toFloat(total)) as deptPercentage``````

What are you trying to accomplish with your last query? In summary, it looks like you have calculated the total of all the Drivers and then updated the driver relationship with the ratio of the driver's Driver value and the total. As a note, there is no need for the merges. There is an alternative approach.

5 REPLIES 5  Ninja

As a note, you can calculate the sum directly with ‘sum(r.Driver)’ instead of apoc.coll.sum(collect(r.Driver)).

what is it you want to calculate? Do you want the total, or the total by driver, and/or ratio of driver total divided by total for each driver? Node Clone

Thank you for taking the time , I would like to create a division of each  driver  by the total Node Clone

I successfully generated the allocation with the query below.
is there a simple way to achieve the same results without collect and index

MATCH p=(n:Account{Account:'Acct 6100'})-[r:Driver]->(d:Department)
with  sum(r.Driver) as sum,collect(d.Department) as dep,collect(r.Driver) as driver,n.Amount as amount
unwind range(0,size(dep)-1) as index
merge(e:Department{Department:dep[index]})
merge(p:Account{Account:'Acct 6100'})
merge(p)-[r:Driver]->(e)
SET r.allocation=(toFloat(r.Driver)/sum)*amount
return r.allocation,r.Department  Ninja

I am not sure what you are looking for, but here is an example of calculating the driver total by department and then dividing each by the total across all departments.

``````match (n:Account{Account:'Acct 6100'})-[r:Driver]->(d:Department)
with n, d.Department as dept, sum(r.Driver) as deptDriverTotal
with n, collect({dept: dept, total: deptDriverTotal}) as deptStats, sum(deptDriverTotal) as total
unwind deptStats as deptStat
return n.amount as amt, deptStat.dept as department, deptStat.total as deptTotal, 100 * (toFloat(deptStat.total) / toFloat(total)) as deptPercentage``````

What are you trying to accomplish with your last query? In summary, it looks like you have calculated the total of all the Drivers and then updated the driver relationship with the ratio of the driver's Driver value and the total. As a note, there is no need for the merges. There is an alternative approach. Node Clone

Thank you!  Nodes 2022 NODES 2022, Neo4j Online Education Summit

All the sessions of the conference are now available online

Neo4j Resources