Find hierarchy based on two columns present in single node

Neo4j Graph Platform Cypher
1

2024-05-23_20-38-51
2024-05-23_20-38-51609×725 9.12 KB

Hi, I am new to Neo4j and struggling to write a cypher query which returns a multi-level hierarchical data.
I have attached a snap of sample data. The node consists of three columns/properties and I want to write query which will check the subitem from column2 (sitm) in column1 (mitm) and do this until any subitem from column2 (sitm) is present in column1 (mitm) till the last level of hierarchy. I want to display all the levels in separate columns like level1, level2 and so on along with their quantities.

2

Have you imported this data and related the adamant and child nodes with a relationship?

3

I have created a node in and added this data but have not created any relationship yet.

4 
Try this:
match (a:Test)
match (b:Test) where b.mitm = a.sitm
return a, b
5

You should create relationships when you import your data, so you can easily traverse your graph. You don’t want to perform a join each time you query. You should use a relational database if that is your need.

If I understand correctly, simt indicates its parent node, which is identified in mitm.

Leveraging @ameyasoft’s query, we can modify it to create relationships. Of course, change “Test” label to the label you used when creating your nodes.

Match (a:Test)
Match (b:Test) 
where b.mitm = a.sitm
Merge(a)-[:HAS_PARENT]->(b)

Now you can query to get all the child nodes for a given root node. The following should return the root node (repeated each row), and each child node with its depth from the root.

Match(n:Test{mitm: “XYZ”})
Match p=(n)<-[:HAS_PARENT*]-(b:Test)
Return n as rootNode, b as childNode, length(p) as depth

This should be as start. If you want the tabular view, you can use this query after the relationships are created.

Match (n:Test)<-[:HAS_PARENT]-(b:Test)
Return n as parentNode, b as childNode
6

image
image1550×674 109 KB

I have tried to explain the scenario in a much better way here. Let me know if you still have any questions. I tried the above queries but that doesn't seem to be working.

7

Can you post the spreadsheet data?

I understand the hierarchy. In my opinion, the rollup of the quantity has to be done in realtime each time you query; otherwise, you will have to update the node and all the nodes ancestors each time a node's quantity changes. Do these quantities change once the node is added? I assume they can be updated.

8
mitm sitm qty
AXXXXXXSA00098761-005 SA00086076-005-601 2
AXXXXXXSA00098761-005 SA00086076-005-602 1
AXXXXXXSA00098761-005 SA00086076-005-603 4
SA00086076-005-601 SA00086076-005-1B 1.838
SA00086076-005-601 SA00086076-005-1C 3.382
SA00086076-005-601 SA00086076-005-1D 2.765
SA00086076-005-601 SA00086076-005-1E 1.838
SA00086076-005-601 SA00086076-005-1G 0.277
SA00086076-005-602 SA00086076-005-602B 1
SA00086076-005-602 SA00086076-005-602C 1
SA00086076-005-602 SA00086076-005-1L 2
SA00086076-005-602 TIS-361B3013-M 0.2
SA00086076-005-603 SA00086076-005-603B 1
SA00086076-005-603 SA00086076-005-603C 1
SA00086076-005-603 SA00086076-005-1L 2
SA00086076-005-603 ZZZ-361B3013-M 0.2
SA00086076-005-1B ZZZ-361B3013-M 2
SA00086076-005-1C ZZZ-361B3013-M 0.2
SA00086076-005-1D ZZZ-361B3013-M 0.407
SA00086076-005-1E ZZZ-361B3013-M 0.223
SA00086076-005-1G ZZZ-361B3013-M 0.4
SA00086076-005-602B XXX-760G9030100020-M 0.5
SA00086076-005-602C XXX-760G9030100020-M 0.5
SA00086076-005-603B 867-316J0210 0.3
SA00086076-005-603C 867-316J0210 0.3

This is data. Also, to answer your question. Quantities will not be changed ones they are added. The last level item quantity should be multiplied with the respective sub level item quantity to show the total quantity.

9

Should the third-level item 'ZZZ-361B3013-M' be repeated like it is in the table? This means it has multiple parents. Is this correct? if so, how will you calculate its quantity? Also, why in your example for the quantity calculation does it not also include quantity "2" from the first level node "SA00086076-005-601"?