row comparison

Hi @takbb

it’s working thanks a lot

1 Like

Hi @takbb ,

small doubt it’s working fine but is there condition shall we put that all the conditions need to passed that definitley need to compare with 2 rows .

i mean if already 2 rows got output “YES”

but our conditions again it is taking and comparing with already row got “YES”

Hi @Abhiram

Yes you can do this, but I agree the syntax is not immediately obvious.

If you want to say $x$ != "Y" you actually write it as NOT $x$ = "Y"

so for example

$Activity$="PART" AND $Activity(Previous)$="REIN" AND $Cash Location$=$Cash Location(Previous)$ AND (NOT $Is Contra$="Y") AND (NOT $Is Contra(Previous)$ ="Y" ) =>"Yes"

I’ve put the NOT clauses in parentheses as I like to keep them self- contained and it makes the intent explicit.

1 Like

Just to check my understanding, are you saying that you might have a situation where 3 adjacent rows all match one or more conditions in which case you’d only want the first two to match and the third row should not now be compared with the second row, because that has already been matched with the first row?

Hi @takbb ,

Yes Takbb

1 Like

Hi @Abhiram , that came as a bit more of a challenge! :wink:

This had me scratching my head and I couldn’t see any easy way of doing this using the standard (non-scripting) nodes.

What I came up with was that having determined whether there was a match to previous or next row, a subsequent processor would have to go down the list of matches and decide which were now allowed, and which weren’t. To perform this process, I had to use a java snippet. There is a feature of the java snippet that allows us to retain some “state” between rows, so we can base actions on what we did during the processing of the row immediately prior to the current row. For further info and examples on that specific feature see the following post

But for that to work, I had to now have two Rule Engines, one for deciding if there was a match to previous row, and the other separately deciding if there was a match to next row. It also meant that the Rule Based Row Filter had to be removed, as the Java Snippet needed to work on the whole data set, and not just on the subset of rows if it were to work properly.

So the Rule Based Row Filter had to be removed, and the rules from there added (with slight modification) to each of the two new Rule Engines.

The upside was that a re-joining of the data set was no longer required.

I discovered a bug that made no difference previously, in the two joiners used for “lagging” the rows. The join columns were round the wrong way so the “previous” row columns were wrongly named (next) and the “next” row columns were being named (previous). This didn’t matter before, but it was wrong and would be very confusing with the two rule engines and the new java snippet referring to “next” and “previous”…

So unfortunately that small additional feature caused quite a dramatic change to the workflow design. It has to be said that row-comparisons isn’t KNIMES strongest point, and I would argue that it is quite common to want to compare rows, especially prior and next rows. It would be nice to have some kind of multi-row Rule Engine, some way of supporting such use cases.

Anyway, here is a new version of the workflow. It would be nice if it were to just “slot in” in place of the previous one, but I suspect you’ll either have to edit it, or else use a column renamer node to rename your input columns to match this workflow, and then rename them back again at the other end… :wink:

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

As before, this needs testing again if you are going to make use of it for real work! Hope that helps.

1 Like

hi @takbb,

thanks it got executed

1 Like

small help needed @takbb,

Actually, i want to copy the values in columns: a) Business Date
b)Feed ID
c)Record ID
d)LE code
e)ETID
f)Exception Discription

from WORK_EXAMPLE (Excel file) and need to paste that values into sheet name : Tracker in
Common_file.xlsx (9.5 KB)
COMMON_ file(excel file)

work_example.xlsx (9.6 KB)
where if COMMON_example excel file inside the sheet of Tracker has some formulaes also.

–>task need to copy and paste the data from columns in one excel file to another excel file without changing the format .

→ exactly it needs to append to that file(coomon _file)

And also if a excel sheet has column containing some mails,

how can i add those to in send mail node in knime

mail.xlsx (9.3 KB)

please can anyone try to help me?

Hi @Abhiram , I’ll take a look at your posts when I get a chance but I’m unfortunately busy with work at the moment so hopefully somebody else might be able to assist.

For your additional question about sending emails, as that is really a new question, you’d be more likely to get responses if you ask that directly as a new topic on the forum as it is a different subject.

With it buried under “row comparison” people who know more about sending emails might not come looking at it here. By all means refer back to this post for context.

I know we said before about not duplicating the question but in this case it’s actually a new question, so best to ask it as a new topic. Hope that makes sense :wink:

3 Likes

Hi Takbb,

can you help me on this ?

For mails topic i had done that

Hi @takbb,

Firstly thanks for the help which you previously given the solution for comparision task,

Right now my team had given some extra conditions for the same task total 8 conditions.

i had just added those extra conditions but it is not working properly,

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

in the above knime workflow i had used all the conditions, plz help me on this.

i am facing the error in

where i highlighted the part those rows need to value of No because as i am having the condition was IF ETID = ETID(Previous) && CASH LOCATION != CASH LOCATION && CURRENCY == CURRENCY && AMOUNT == AMOUNT && ACTIVITY = ACTV , ACTIVITY =CXLD

i had attached the knimw workflow with all 8 conditions in rule engine.

As i understand you are all busy with your own works, Apologies please consider my request and help me :slight_smile:

Hi @Abhiram -

I took a look here, but need more clarity about what you’re trying to do. There is some ambiguity in your statement above - for example CASH LOCATION != CASH LOCATION can’t be right, you must have meant to include a previous or next in there. Same for some of the other columns.

Maybe it would look something like this? It’s hard to tell based on what you’ve you described.

// 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"
2 Likes

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

Hi Team & @takbb @bruno29a @ScottF

Please help me on this

Hi Team,

please anyone gives some suggestion on this

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… :wink:

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.

1 Like

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