Create relationship based on another column

[leowyokeling]

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!

[glilienfield · Accepted solution]

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

 

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

[leowyokeling]

Hi Glilienfield,

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