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!