cancel
Showing results for 
Search instead for 
Did you mean: 

Join the community at Nodes 2022, our free virtual event on November 16 - 17.

Create relationship based on another column

Hi all, I am a beginner in Cypher and would like to seek for some help here.


I have data loaded from CSV as below:

AccountID

TransactionID

FirstTransactionID

AID1

TID1

Null

AID1

TID2

Null

AID1

TID3

TID3

AID1

TID4

Null

From the data above, the TransactionID is the unique ID assigned when an account makes a transaction while FirstTransactionID returns the TransactionID for each account when it is the first transaction the account made, for example, out of all four transactions here, TID3 is the oldest transaction for AID1, thus returning TID3.

I would like to create a new relationship called FIRST_TX between AccountID and TransactionID only when TransactionID is same as FirstTransactionID. The reason I did not create the relationship directly to FirstTransactionID is because I have an existing relationship between AccountID and TransactionID, so I do not want some new nodes to be created based on FirstTransactionID within the graph.

My query is as below. May I ask if there is anything I can improve to create the relationship for FIRST_TX?

LOAD CSV WITH HEADERS FROM "http://localhost:11001/project-dbb97a21-aa76-437d-9fbb-04902fc94d8a/Transaction_Sample.csv" AS trx FIELDTERMINATOR ';'
MERGE (tid:transactionid {id:trx.TransactionID})
MERGE (aid:accountid {id:trx.AccountID})
MERGE (fid:firsttransactionid {id:trx.FirstTransactionID})
MERGE (aid)-[:TRANSACTED]->(tid)
MERGE (aid)-[:FIRST_TX]->(fid)

Thank you all!

1 ACCEPTED SOLUTION

glilienfield
Ninja
Ninja

Do you really need a new node for the first transaction, as it really is not a transaction.  You will not have any additional transaction properties to store in this node. It is just a special charactistic of an actual transaction.  How about just adding an additional label on the transaction to indicate it was the first transaction, such as 'FirstTransaction" or "First"?   I have a similar comment for the second relationship, as it will not have any additional relationship properties to track and you already have the account and transaction related. Labeling one transaction for each account as the first will allow you to identify the first transaction. 

I would also label the nodes with labels 'Account' and "Transaction' instead of 'accountId' and 'transactionid', as the nodes represent entities. 

I modified your query to address those items, incase you are interested in my comments. 

LOAD CSV WITH HEADERS FROM "file:///Book2.csv" AS trx 
MERGE (tid:Transaction {id:trx.TransactionID})
MERGE (aid:Account {id:trx.AccountID})
MERGE (aid)-[:HAS_TRANSACTION]->(tid)
WITH tid, trx
WHERE trx.FirstTransactionID = trx.TransactionID
SET tid:First

Screen Shot 2022-08-01 at 11.23.20 PM.png 

Note, the blue node has both a 'First' and a 'Transaction' label. 

View solution in original post

2 REPLIES 2

glilienfield
Ninja
Ninja

Do you really need a new node for the first transaction, as it really is not a transaction.  You will not have any additional transaction properties to store in this node. It is just a special charactistic of an actual transaction.  How about just adding an additional label on the transaction to indicate it was the first transaction, such as 'FirstTransaction" or "First"?   I have a similar comment for the second relationship, as it will not have any additional relationship properties to track and you already have the account and transaction related. Labeling one transaction for each account as the first will allow you to identify the first transaction. 

I would also label the nodes with labels 'Account' and "Transaction' instead of 'accountId' and 'transactionid', as the nodes represent entities. 

I modified your query to address those items, incase you are interested in my comments. 

LOAD CSV WITH HEADERS FROM "file:///Book2.csv" AS trx 
MERGE (tid:Transaction {id:trx.TransactionID})
MERGE (aid:Account {id:trx.AccountID})
MERGE (aid)-[:HAS_TRANSACTION]->(tid)
WITH tid, trx
WHERE trx.FirstTransactionID = trx.TransactionID
SET tid:First

Screen Shot 2022-08-01 at 11.23.20 PM.png 

Note, the blue node has both a 'First' and a 'Transaction' label. 

Hi Glilienfield,

Thanks for your help and especially your comment on the node label!