Using Case to create a node when using Load CSV

[matthew_cash]

I am trying to create two different nodes and relationships based on certain data in a CSV.
I want to be able to create a Node like seen in the script below. Where if "AGGREGATE" is in a specific column then create a Node based off a different column. If "AGGREGATE" is not in that column then do not create a node.

:auto USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'file:///TestFull.csv' AS test
WITH test WHERE test.equip_id IS NOT NULL
WITH test, SPLIT(test.equip_id, '/') as eid,
(CASE granite.path_bandwidth
WHEN 'AGGREGATE' THEN granite.path_leg_name
END) as agg
MERGE (logip:LogiP {logiKey: eid[0] + '.' + agg})

Getting the error:
"Cannot merge node using null property values for logiKey"

If I change the NOT NULL statement to test.path_leg_name or path_bandwidth, I get the same error as well. I do not see any place there there could be a null value. If I remove the NOT NULL line then I still get the same error too.

Any suggestions how I can make this happen?

After I get this to work, I then need to make a relationship based off this.

(p:Port) - [:SUB_EQUIP] -> (l:LogiP) if it exists
if the LogiP was not created from the last statement then it automatically creates this relationship instead:
(p:Port) - [:SUB_EQUIP] -> (s:SubPort)

I haven't been able to find anything to help me with that part.

All suggestions help!

[ameyasoft · Accepted solution]

For the first part try this:

Assuming you have Port node created and got a reference to that node like (p:Port)


FOREACH(ignoreMe IN CASE WHEN test.granite.path_bandwidth = "AGGREGATE" THEN [1] ELSE [] END|

MERGE (logip:LogiP {logiKey: eid[0] + '.' + test.granite.path_leg_name})
MERGE (p)-[:SUB_EQUIP]->(logip) 

)

For the second part try  this:

Assuming you have SubPort node created and got a reference to that node like (s:SubPort) and Pot node (p:Port)


FOREACH(ignoreMe IN CASE WHEN test.granite.path_bandwidth <> "AGGREGATE" THEN [1] ELSE [] END|

MERGE (p)-[:SUB_EQUIP]->(s)

)

[matthew_cash]

So far this is working great but I realized I did fail to mention further details on the second half of this scenario.. one of the columns can have different variables for the data, which causes it to create multiple of Nodes. I am using the code below to create the data:

:auto USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'file:///TestFull.csv' AS granite
WITH granite, SPLIT(granite.equip_id, '/') as eid, SPLIT(granite.port_access_id, '.') as pid
MATCH (p:Port {portKey: eid[0] + '.' + pid[0]})
FOREACH(ignoreMe IN CASE WHEN granite.path_bandwidth = "AGGREGATE" THEN [1] ELSE [] END|
MERGE (logip:LogiP {logiKey: eid[0] + '.' + granite.path_leg_name})
MERGE (sk:subKey {logiKey: eid[0] + '.' + granite.channel_subrate})
MERGE (p)-[:SUB_EQUIP]->(logip) 
MERGE (logip)-[:SUB_INTERFACE]-(sk)
)

:auto USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'file:///TestFull.csv' AS granite
WITH granite, SPLIT(granite.equip_id, '/') as eid, SPLIT(granite.path_leg_name, '/') as agg
MATCH (logip:LogiP {logiKey: eid[0] + '.' + granite.path_leg_name})
MATCH (sk:subKey {logiKey: eid[0] + '.' + granite.channel_subrate})
SET logip.aggId = agg[0]
SET sk.subId = agg[0] + '.' + granite.channel_subrate


:auto USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'file:///TestFull.csv' AS granite
WITH granite, SPLIT(granite.equip_id, '/') as eid, SPLIT(granite.port_access_id, '.') as pid
MATCH (p:Port {portKey: eid[0] + '.' + pid[0]})
FOREACH(ignoreMe IN CASE WHEN granite.path_bandwidth <> "AGGREGATE" THEN [1] ELSE [] END|
MERGE (sk:subKey {logiKey: eid[0] + '.' + granite.channel_subrate})
MERGE (p)-[:SUB_INTERFACE]->(sk)
SET sk.subId = p.portId + '.' + granite.channel_subrate
)

The issue I am running into now is for the channel_substrate it could be "DATA123" "123" or "DATA123.REV"
Is there a way to pull only the numbers out of this column?

[ameyasoft]

You can use regex. Here is the Cypher script:

apoc.text.replace(val, "[^0-9]", "")

MERGE (sk:subKey {logiKey: eid[0] + '.' +  apoc.text.replace(granite.channel_subrate, "[^0-9]", "")})

[intouch_vivek]

Hi @matthew.cash,

Where did you get granite from?
To check the null value in your case just try to test using
:auto USING PERIODIC COMMIT 5000
LOAD CSV WITH HEADERS FROM 'file:///TestFull.csv' AS test
WITH test WHERE test.equip_id IS NOT NULL
WITH test, SPLIT(test.equip_id, '/') as eid,
(CASE granite.path_bandwidth
WHEN 'AGGREGATE' THEN granite.path_leg_name
END) as agg
MERGE (logip:LogiP {logiKey: coalesce(eid[0],'NULL equip_id') + '.' + coalesce(agg,'Null in other part'})

and this way you can find the issue