Traversing lineage and deriving value using Cypher

[inigabs]

Hi ,

I'm fairly new to Neo4j but already appreciate the potentials it may have posses. And I've made quite a bit of progress in a short month or so playing around with it after installing the Desktop version. The use case I'm trying to work with is like a member data table where members are referred to become members. One may only be referred or recognized as a member by referral from another member.

So I have created the node Member and imported the data into it. I have also created relationship REFERS_DL that references itself. That means a memberId is referred by another member who is indicated in the referralId, except the top node or first person who does not have a referralId. The sample member data and node visualization and relationship are shown below:

The business logic requires that lineage data/nodes be generated for each members' organization using the data in the member node. Along with this lineage, the Type Level be derived as well. The business logic for the Type Level stipulates that the level starts with 1 (if memberType = A) or 0 (if memberType = C) for the origin node of the organization/lineage being processed, and the level only increments by 1 for each leg when it encounters a downline member in the lineage that is of member type 'A', otherwise, it inherits whatever member level was assigned to its predecessor.
Below shows the lineage for organization with origin node Ini(meberId = 100) and the lineage data with the expected MeberLevel:

So the challenge I have is how the generate the MemberLevel. I am using the Cypher query below to generate the lineage:
MATCH(m1:Member)-[r:REFERS_DL*0..]->(m2:Member)
WHERE m1.memberId = '100'
WITH m1.memberId AS OriginId,m1.name AS OriginNode,m2.memberId AS MemberId,m2.name AS Member,m2.referralId AS ReferralId,m2.name AS ReferredBy, m2.memberType AS MemberType
RETURN OriginNode,Member,ReferredBy,MemberType

Is it possible to incorporate a logic in the query that can generate MemberLevel and what would that logic be? Or does that need to be handled differently?

Really looking forward to your suggestions and assistance in this.

Thanks in anticipation.
Ini

[andrew_bowman]

Can you supply the CSV and the Cypher that we can use to generate that graph? That gives us something concrete to play with.

[inigabs]

Thanks for your response Andrew. On reviewing my post, I realized there were some mistakes. Please see the corrected data you requested and the code used:
MemberData1.txt (532 Bytes)

Please note that I could not upload the csv because the upload process does not allow csv but allows txt. But I used the csv format to import the data from my system into Neo4j with the code below:

LOAD CSV WITH HEADERS
FROM 'file:///MemberData1.csv'
AS line
MERGE (m:Member {memberId: toInteger(trim(line.MemberId))})
ON CREATE SET m.name = trim(line.MemberName), m.referralId = toInteger(trim(line.ReferralId)), m.memberType = trim(line.MemberType)
ON MATCH SET m.memberId = toInteger(trim(line.MemberId))

Then I ran the script below to create the self relationship:
MATCH(m1:Member),(m2:Member) where m2.referralId = m1.memberId CREATE (m1)-[:REFERS_DL {level:1}]->(m2) return m1,m2

The script below returns the lineage:
match(m1:Member)-[:REFERS_DL*0..]->(m2:Member) RETURN m1,m2

The script below returns the lineage in a table form and is the one I am trying to use to implement/derive the MemberLevel based on business logic I explained earlier that needs to increment by 1 when it encounters a downline/member of type 'A' and inherits the level of the predecessor when the type is 'C':

MATCH(m1:Member)-[r:REFERS_DL*0..]->(m2:Member)
WHERE m1.memberId = 100
OPTIONAL MATCH(m3:Member)
WHERE m3.memberId = m2.referralId
WITH m1.memberId AS OriginId,m1.name AS OriginNode,m2.memberId AS MemberId,m2.name AS Member,m2.referralId AS ReferralId,m3.name AS ReferredBy,size(r) AS Level,m2.memberType AS MemberType
RETURN OriginNode,Member,ReferredBy,MemberType,Level
ORDER BY Level,ReferredBy

The level displayed here is just the depth from the origin.

Thanks for your anticipated response.
Ini