Ok, got it @Abhiram , so Row2 would not be able to match with Row4 in this case as Row4 would already have been matched and flagged as Yes for Cleared.
in the above example where Transaction SubType of ROW1 & ROW2 must be Same like (if ROW 1 has BUY then again ROW 2 also should be BUY) this point i already mentioned in my conditions
----> only 9th condition alone Transaction SubType should be for 1 row was BUY and another row was SELL
â> where 9th condition will perform only when ever ETIDS start with CS,CT
TRACKER_AE.xlsx (10.6 KB)
if you need sample excel sheet please refer this
and also ETIDs should be different
One more question @Abhiram , for Case B and Case D, which Row or Rows does the Is Contra flag apply to? Should both rows have that flag?
EDIT: My understanding is that the 9th condition, which is CASE I is to be ignored. I implemented something with the rules from Case A to H. Could it be that the data you provided does not fall in any of these Cases?
Hi @Abhiram
Also, to make your rules a bit simpler, your 8 cases can be summarized in these 4 cases:
Same ETID, Currency, Amount, Transaction Sub type, and:
1)Case A and C:
a) Same Cash location
b) Activity: (SETT or PART) and REIN
2)Case B and D:
a) Same Cash location
b) Activity: (SETT or PART) and SETT with Is Contra Flag = Y
3)Case E and F:
a) Activity: ACTV and (CXLD or CXCR)
4)Case G and H:
a) Activity: MOD and (CXLD or CXCR)
Itâs a bit clearer and validation from Knime will be faster rather having to check for Same ETID, Currency, Amount, Transaction Sub type 8 times as opposed to just once. Similarly for Cash location where it will check twice as opposed to 4 times.
Can you please confirm what âwith Is Contra Flag = Yâ means, and also provide some data that will actually match some of these cases?
EDIT, even the Case E, F, G, and H could be done in 1 condition:
a) Activity: (ACTV or MOD) and (CXLD or CXCR)
So, 8 Conditions summarized in 3 
Same ETID, Currency, Amount, Transaction Sub type, and:
1)Case A and C:
a) Same Cash location
b) Activity: (SETT or PART) and REIN
2)Case B and D:
a) Same Cash location
b) Activity: (SETT or PART) and SETT with Is Contra Flag = Y
3)Case E, F, G and H:
a) Activity: (ACTV or MOD) and (CXLD or CXCR)
Hi @Abhiram
Hereâs something I put together in Python. I have both versions with original Cases, and re-arranged cases. They should give the same results, but the data that you provided does not match any, so I cannot test this. The re-arranged version should be faster.
Row Comparison.knwf (20.4 KB)
Regarding the âIs Contra Flag = Yâ for Case B and D, I have made the assumption that both rows should have Is Contra Flag = Y
EDIT:
Suggestion:
Run a sample of your dataset using both ways and see if you get the same result. If they provide the same result, then it means that I have summarized the cases correctly, and you can use the re-arrange cases version for your bigger dataset.
Some additional notes:
- The reason I used Python is because we need to access several other rows while processing a row. The Lag Column node would give âaccessâ to only another row and for only one column at a time. It would have to be repeated for each row, and for each column, which can be quite challenging. For this kind of âaccessâ, I prefer doing this in Python.
- Even if I somehow am able to put all this in a Loop in Knime, chances are I might not be able to capture the changes of the modified Cleared values of other iterations during the loop to be able to ignore the rows that changed to Cleared Yes during previous iterations of the loop.
- Iâm not a Python developer, perhaps there might be a better way to write this, but I think the task is simple enough to get by - compare and assign value
1 Like
thanks @bruno29a,
the above python implementation, is working fine for the sample data but i changed some data in that excel file where i found an issue that
TRACKER_AE.xlsx (12.0 KB)
it is comparing with ROW 2 with ROW 5 and getting values as YES where the two Rows having different legal entitites like one is 002, 0CG it should not be compare with two different Legal entities.
and also is there any way to implement the 9th condition need to execute in the workflow
Hi Team,
Is there any chance how can we implement for the 9th condition in the workflow
@bruno29a Is there any way that we can use python code
-----> without updating the conda version
-------> presently anaconda version was 4.5 in my lap
â> anaconda version was required as 4.6.2
Hi @Abhiram
I implemented the rules based on the 8 Cases that you gave. None of them mention the Legal entities column. The columns mentioned in them are ETID, Currency, Amount, Transaction Sub type, Activity and Is Contra Flag.
Iâm not sure how I was supposed to know that the Legal entities column should also be compared.
EDIT:
Does this mean that you added the condition about the Legal entities, and itâs resolved?
Can you properly state what the 9th condition is? And please make sure that ALL the rules are included
Hi @bruno29a ,
with adding some modification on Legal entities the issue got resolved.
----> i already specified the 9th condition which is case I: in my previous post
i.e : 9th condition Case I:
a) It should be same Cash location, Currency, Amount
b) different ETIDS ( where ETIDS should start with CS, CT then only this condition should use)
c)Transaction Sub Type BUY and SELL
where i tried that 9th condition in the workflow but i am struck at the point how can use the code for
where ETIDS should start with CS, CT then only this condition should use
for your reference
Row Comparison_Python.knwf (27.7 KB)
please check the node 6
Hi @Abhiram
Yes, with the 8 other conditions which turned out were missing the Legal entities condition, so I want to make sure that whatever you already specified was indeed correct.
What does these mean?
b) different ETIDS ( where ETIDS should start with CS, CT then only this condition should use):
Does this mean one should be CS and the other CT?
c)Transaction Sub Type BUY and SELL:
Does this mean one has to be BUY and the other SELL?
Hi @bruno29a ,
b) it means where some ETIDS having some values where those are starts with CS and CT,
if an excel sheet having some 10 rows consists 5 rows with ETID values are starts with CS or CT and other 5 rows are like not start with CS or CT
example:
TRACKER_AE.xlsx (12.0 KB)
in SHEET 1 some rows of ETIDs start with CS and CT like that
comparision need to be done for that ETIDS need to be different
c) one row must be BUY and another row must be SELL
like ROW 1; BUY or SELL
ROW 2: SELL or BUY
Hi @Abhiram
This is a bit confusing⌠I mean why are we talking about 10 rows? The comparison is done between 2 rows at a time, right? So all I want to know by this rules is: does it mean that 1 row should start with CS and the other with CT? Or it does not matter as long as it starts with either CS or CT on both rows?
For example,
Row1 has CS0056tyhu and Row2 has CS56tyhu, is that ok for matching? Or can CS0056tyhu only match with CT0056tyhu (or CT00886tyhu)?
Hey @bruno29a, I may be wrong but I think that back at the beginning of this incredible journey, âlegal entityâ wasnât mentioned as part of the things to compare 
. In fact in a screenshot I posted (shortly after the Big â â â â , I think, lol) in an attempt to clarify things, Legal Entity was noted as âanyâ⌠but⌠there has been⌠how do i put it⌠âscope creepâ 
Edit⌠huh?.. the term Big B a n g is redacted! 
2 Likes
Hi @bruno29a,
â> Just for example taking 10 rows
---->Yeah It does not matter as long as it starts with either CS or CT on both rows but both ETIDS should be different
Thanks for confirming @Abhiram .
I modified my workflow, for both Original Cases and re-arrange cases to cover the following:
- Added rules for Legal entities for Cases A to H
- Added rules for Case I
- Added code to disable warning about SettingWithCopyWarning
Hereâs the updated workflow: Row Comparison.knwf (21.6 KB)