row comparison

[Abhiram]

Apologies @ScottF,

For Late response,

Actually i am tryting to compare two rows in an excel sheet with the given conditions it means

Consider In a excel has 10 rows data,

below case example for just (1 condition)

where the task need to perform as ROW 1 need to compare with ROW 2 and check with respective columns like ROW 1 – ETID (SAME) of ROW 2 (ETID) then check ROW 1-- Cash Location (Same) of ROW 2(Cash location) like wise ROW 1— Currency(Same) of ROW 2 Currency and also ROW 1 — Amount (SAME) of ROW 2 AMount then ROW 1 Activity must be SETT and ROW 2 must be REIN at last ROW 1 and ROW 2 Must be either BUY or SELL

like that it need to check if it didn’t matched ROW 1 and ROW2 then the column Cleared?(Yes/No) it remains No, If it matches ROW 1 and ROW 2 then it need to change as Yes

in case ROW 1 didn’t matches ROW 2 then it need to go with ROW 1 & ROW 3 like that if again ROW 1 didn’t matches with ROW 3 it need to check with ROW 1 to ROW 4 -------- if some where ROW 1 matches with ROW 6 then ROW 1 and ROW 6 values of column Cleared(Yes/No) that both ROW 1 and ROW 6 should be Yes then after that it need to check from ROW 2 and ROW 3 same process if ROW 2 and ROW 3 matches then it need to compare with ROW 4 and ROW 5 like that , if ROW 4 and ROW 5 didn’t match then it need to check for ROW 4 and ROW 7 because already Row 6 got value as Yes, so it need to check with Row 4 and Row 7

Conditions:
1)Case A
a) Same ETID,Cash location,Currency,Amount
b)Activity: SETT and REIN
c)Transaction Sub type is same for all Activity(BUY/SELL)

2)Case B
a) Same ETID,Cash location,Currency,Amount
b)Activity: SETT and SETT with Is Contra Flag = Y
c)Transaction Sub type is same for all Activity(BUY/SELL)

3)Case C
a) Same ETID,Cash location,Currency,Amount
b)Activity: PART and REIN
c)Transaction Sub type is same for all Activity(BUY/SELL)

4)Case D
a) Same ETID,Cash location,Currency,Amount
b)Activity: PART and SETT with Is Contra Flag = Y
c)Transaction Sub type is same for all Activity(BUY/SELL)

5)Case E
a) Same ETID,Currency,Amount
b)Activity: ACTV and CXLD
c)Transaction Sub type is same for all Activity(BUY/SELL)

6)Case F
a) Same ETID,Currency,Amount
b)Activity: ACTV and CXCR
c)Transaction Sub type is same for all Activity(BUY/SELL)

7)Case G
a) Same ETID,Currency,Amount
b)Activity: MOD and CXLD
c)Transaction Sub type is same for all Activity(BUY/SELL)

8)Case H
a) Same ETID,Currency,Amount
b)Activity: MOD and CXCR
c)Transaction Sub type is same for all Activity(BUY/SELL)

9th condition:
CASE I)
a)Same Cash location, Currency, Amount
b)Different ETIDs — ETID must start with CT,CS only
c)Transaction SubType: BUY and SELL for this condition it must be one row contains BUY and another need to be as SELL

As per above instructions i tried with the flow but it is not work as like that
Set value on lagged row multi-column-conditions with adjacent rows.knwf (41.9 KB)

the above work flow where

ROW 0 and ROW 3 are matching with given condition of 1 is following but Cleared coloumn it need to be as Yes , i mean ROW 0: Yes
ROW 3: Yes

for your reference i am adding one more excel sheet.
TRACKER_AE.xlsx (10.6 KB)

Please help me on this

[Abhiram]

Hi Team & @takbb @bruno29a @ScottF

Please help me on this

[Abhiram]

Hi Team,

please anyone gives some suggestion on this

[takbb]

@ScottF

// condition 9:
($ETID$ = $ETID(Previous)$) AND (NOT $Cash Location$ = $Cash Location(Previous)$) AND (NOT $Currency$ = $Currency(Previous)$) AND (NOT $Amount$ = $Amount(Previous)$) AND ($Activity$ IN ("ACTV","CXLD")) => "No"

Hi @Abhiram, the rule you have given in your more recent post for “rule 9” is nothing like how you expressed it in the earlier post. You are now referring to the transaction subtype must be one BUY and one SELL and you also say that ETID must start with CT or CS. You also now make no mention of the Activity ACTV/CXLD, which appeared to be key in your earlier post.

Can you please state the rule in full. I believe the answer that @ScottF suggested was on the right lines for your earlier post, but it is no surprise it is wrong for your requirement, if your requirement actually is different to what you said…

Even though I looked at this before, I’m now confused about what your new requirement actually is, so it is difficult to assist here.

As you said you tried adding the rules but couldn’t make it work, maybe it would be a good idea to post the actual rule engine rules you have tried to add, and we can look to see where the problem is.

[Abhiram]

Thanks for the reply @takbb,

i had added the work flow with 8 conditions where the past requirement given by my team.

and now they had given a new requirement that condition 9 was
a)Same Cash location, Currency, Amount
b)Different ETIDS,
c)Transaction SubType: BUY and SELL
(where ETIDS should need to be start with CS,CT)

the above condition has given a new requirement to me.

And also for the old requirement which you had given approach was fine but if i used the same workflow now

Set value on lagged row multi-column-conditions with adjacent rows.knwf (41.9 KB)

i am facing the issue:

As per conditions i used in the RULE ENGINE NODE, RULE BASED FILTER NODES, where ROW 0 and ROW 3 need to be Yes & Yes

[ScottF]

Does this get you any closer?

// condition 9:
(NOT $ETID$ = $ETID(Previous)$) AND ($Cash Location$ = $Cash Location(Previous)$) AND ($Currency$ = $Currency(Previous)$) AND ($Amount$ = $Amount(Previous)$) AND ($Transaction Sub Type$ IN ("BUY", "SELL")) => "No"

I’m a little confused why your latest workflow doesn’t already include a rule for condition 9. What have you tried yourself?

Your latest post is additionally confusing because the results from the workflow you provide, for me at least, don’t match your screenshot above.

If you are still having trouble, please try to simplify as much as possible so we can zoom in on the problem.

(Also, please don’t spam the forum with multiple requests for help within hours. We need a little time to digest to try to help )

[Abhiram]

Hi @ScottF,

Can you please ignore the

// condition 9:
(NOT $ETID$ = $ETID(Previous)$) AND ($Cash Location$ = $Cash Location(Previous)$) AND ($Currency$ = $Currency(Previous)$) AND ($Amount$ = $Amount(Previous)$) AND ($Transaction Sub Type$ IN (“BUY”, “SELL”)) => "No"

I Had already modified with that condition in below workflow
Set value on lagged row multi-column-conditions with adjacent rows (3).knwf (41.5 KB)

[ScottF]

In your latest workflow, your Rule-based Row Filter is generating an empty table. Is this what you intend? I suspect not, since then your Rule Engine doesn’t have anything to work with.

For example, this statement in your Rule-based Row Filter doesn’t make sense:

$Transaction Sub Type$="BUY" AND  $Transaction Sub Type$="SELL" => TRUE

Shouldn’t that be an OR statement instead? Are you certain you fully understand the syntax to implement your rules?

[Abhiram]

i tried with OR it didn’t be changed out still the same issue:

[Abhiram]

May be we need to use Python snipet node or Java snipet node between the work flows for the row by row comparision

[bruno29a]

This is one of the longest thread I’ve ever seen on the forum

@Abhiram , I still do not fully understand the rules to be honest, and I think that’s what the issue has been for this thread to be so long.

One more question, you mentioned that if Row1 does not match Row2, then compare Row1 to Row3, etc… and finally let’s say Row1 matches with Row6, we stop doing comparison for Row1. After that, we start over with comparing Row2 to Row3, etc… What happens if Row1 actually matches Row2? As I understand it, comparison for Row1 stops. What would be the next starting point? Row2 compared to Row3? Or would it start with Row3?

It sounds also that you are going by trial and error (though some times it can happen when we run out of options).

As highlighted by @ScottF $Transaction Sub Type$="BUY" AND $Transaction Sub Type$="SELL" => TRUE will never be true. With an AND statement, it means that $Transaction Sub Type$ to be equal to “BUY” and “SELL” at the same time, which is impossible. And it’s not a matter of “trying” with OR, it should be a matter of understanding the logic

[ScottF]

Right, I didn’t necessarily think that changing that single AND/OR statement would solve the entire problem.

What I’m getting at is that you should look at this problem from a broader context. This will likely involve quite a bit more testing on each condition to make sure they work individually, before trying to combine them.

In order to do that you need to be confident you understand the Rule Engine syntax, which can be tricky. As @bruno29a stated above there are logical errors going on in the implementation which will hinder ever getting a correct answer.

So I would gently suggest it’s time to take a step back and break this problem into much smaller chunks for better understanding. Certainly I would do that before pivoting to Python or other scripting.

[Abhiram]

Thanks for the reply @bruno29a,

You had asked some questions

1. if Row 1 matches with Row 2 then Row 1 & Row 2 will be “Yes”
   then comparision will start from Row 3(the next starting point would be Row 3)
2. it would start from Row 3
   where Row 3 need to check with Row 4 if it didn’t matches with specific conditions then comparision need to start with Row 3 to Row 5…

[Abhiram]

I think we may need to build the logic through Python May be

[bruno29a]

Hi @Abhiram ,

@Abhiram

• if Row 1 matches with Row 2 then Row 1 & Row 2 will be “Yes”
  then comparision will start from Row 3(the next starting point would be Row 3)
• it would start from Row 3
  where Row 3 need to check with Row 4 if it didn’t matches with specific conditions then comparision need to start with Row 3 to Row 5…

What is the logic behind starting from Row3 in this case? Would it be that you skip the Row that matched? Does this mean in your original example where Row1 matched with Row6, we would skip Row6 in other comparison? Like Row5 would NOT compare with Row6?

On the other hand, what is the impact of NOT skipping any rows? I ask this cause it might be easier to implement something that is not skipping any rows. Also, as I see it, you are not keeping track of which row matches with which row, is that correct?

@Abhiram

I think we may need to build the logic through Python May be

This could be the case, but the main issue here is establishing what the logic should be first. If this is not done, it does not matter how you want to implement it.

[Abhiram]

----->because ROW 1 and ROW 2 we will ignore those rows because the two rows already matched.

----->yeah before it moves to ROW 1 to ROW 6 (firstly it starts with ROW 1 to ROW 2) then if it not matches then (ROW 1 to ROW 3) again if both not matches then (ROW 1 to ROW 4) like wise if ROW 1 to ROW 6 matches then we need to ignore ROW 1 and ROW 6.

-----> then comparision starts from ROW 2 to ROW 3 if these rows matches then comparision need to start with ROW 4 to ROW 5 again if those 2 rows ROW 4 and ROW 5 matches then comparision will be start with ROW 7 to ROW 8 because already ROW 6 with ROW 1 comparsion was done.

[bruno29a]

Thanks for the additional information @Abhiram . I have a few more questions.

I went to look at your conditions again. Can a Row match with more than 1 condition?

For example, can Row1 match with Row2 via Case E, and also match with Row3 via Case F? The only difference here is that Row2 has Activity CXLD and Row3 has activity CXCR. For example:

Business Date	Legal Entity	ETID	Cashlocation	Currency	Amount	Transaction SubType	Activity	Is Contra Flag	Cleared?(Yes/No)
2021-09-23	002	AB	USA	Dollar	12908	BUY	ACTV		No
2021-09-23	002	AB	AUS	Dollar	12908	SELL	CXLD	Y	No
2021-09-23	0CG	AB	JPY	Dollar	12908	SELL	CXCR	Y	No

Based on what you explain for the rule, it will only see the match of Row1 with Row2 and will not see the match of Row1 with Row3 as the comparison will stop once Row1 matches Row2. Is that correct?

But based on the action, which is setting the flag Cleared to “Yes”, it looks like it does not matter if there is more than 1 match, just as long as there is a match.

The question would make more sense if the order of the record is different. Take a look at this example:

Business Date	Legal Entity	ETID	Cashlocation	Currency	Amount	Transaction SubType	Activity	Is Contra Flag	Cleared?(Yes/No)
2021-09-23	002	AB	USA	Dollar	12908	BUY	CXLD		No
2021-09-23	002	HJ	AUS	Dollar	12908	SELL	CXCR	Y	No
2021-09-23	0CG	OP	JPY	LIO	555	SELL	MOO	Y	No
2021-09-23	0CG	AB	SIN	Dollar	12908	BUY	ACTV		No

Row1 and Row4 match as Case E. Based on what you said, in the next comparions, Row1 and Row4 should be ignored. But you can see that Row2 and Row4 actually match as Case F. But if Row4 is ignored, that match will not be detected. Is this the expected behaviour?

[Abhiram]

Apologies @bruno29a,

where in the above case ROW 1 has Legal entity was 002 and ROW 4 has 0CG it should not be consider ( we should not do comparision for like that)

—> comparasion should be done where Same legal entity

—> for your question the answer was ROW 1 matches with ROW 2 then ROW 1 need not be compare with ROW 3 where Row 3 has different legal entity

----> And also already matched any two rows then we should not consider those rows again

these information our managers had given the task

[bruno29a]

Hi @Abhiram , sorry I forgot to change the ETID, but let’s say that Row1 match with Row4 based on conditions of Case E, and Row2 match with Row4 based on conditions of Case F, would Row 2 still be able to match with Row4? Based on what you explained, Row4 would be ignored if it’s match with Row1.

EDIT: Here’s the proper example:

Business Date	Legal Entity	ETID	Cashlocation	Currency	Amount	Transaction SubType	Activity	Is Contra Flag	Cleared?(Yes/No)
2021-09-23	002	AB	USA	Dollar	12908	BUY	CXLD		No
2021-09-23	002	AB	AUS	Dollar	12908	SELL	CXCR	Y	No
2021-09-23	0CG	OP	JPY	LIO	555	SELL	MOO	Y	No
2021-09-23	002	AB	SIN	Dollar	12908	BUY	ACTV		No

[Abhiram]

Hi @bruno29a ,

where comparision will starts first legal entity column will check like in your example case ROW 1 and ROW 4 Legal entities are different then comparision need not should consider.

your 2nd example if ROW 1 and ROW 4 first it matches then we should not again consider ROW 2 with ROW 4 because ROW already GOT values “Yes”

—> once any rows got “Yes” it should not be used for again comparision