Continuing the discussion from row comparison:
Hi @bruno29a ,
Need another help from the same article,
Got another case (or) condition from the same task 
–>1) There should be no limit in terms of the rows/lines for comparision as long as it meets the below criteria:
a) Same ETID, Cash Location, Currency
b) Transaction Sub type is same for all Activity(BUY/SELL)
c) There are Two (or) Four (or) More REIN/SETT (Activities) to cancel out the Amount of one SETT/REIN(Activity)
below data for further analysis:
TRACKER_AE.xlsx (12.1 KB)
The Lines highlitted in Blue will Match off the amount in Red hence, we should tag this as “Yes” in Cleared?(Yes/No) coloumn.
use above excel file for example data (Sheet 1).
where as check (Manual sheet ) how the output requires
Can anyone help me on this??
@bruno29a ,
Hi @Abhiram , I’m a bit busy with work these days, I am not sure when I’ll be able to get to it.
Hi @Thyme ,
Do you able to help on this?
it’s a Row comparision, but difficult to makes a condition for the above case through any nodes in knime.
Can anyone provide some ideas
Hi @abhiram -
I think you are most likely to get some assistance if you post the current state of your workflow and what you have already tried. Especially given that your previous thread and task were fairly complicated, and people here are donating their time and expertise if they elect to help.
6 Likes
Hi @ScottF,
As of now i am at here
Row - wise - Comparison.knwf (22.1 KB)
i got a new task with the below case:
- There should be no limit in terms of the rows/lines for comparision as long as it meets the below criteria:
a) Same ETID, Cash Location, Currency
b) Transaction Sub type is same for all Activity(BUY/SELL)
c) There are Two (or) Four (or) More REIN/SETT (Activities) to cancel out the Amount of one SETT/REIN(Activity)
TRACKER_AE.xlsx (12.1 KB)
The Lines highlitted in Blue will Match off the amount in Red hence, we should tag this as “Yes” in Cleared?(Yes/No) coloumn.
work example : Sheet1(take this as example)
manual output : Sheet2 (output)
i need to get the output from Knime as Sheet2
Actually I am struck at the stage where i cannot able to make a statement in python : “Consider if 7 rows in column AMOUNT where for Sum of 5 rows of same/different data was = 12000 then need to compare if 6th rowdata = 12000 or not and also Column ACTIVITY need to be for 5 rows of data was either REIN or SETT and the 6th row need to be as either REIN or SETT” how can i make a condition in python ??
including above case as well
→ There should be no limit in terms of the rows/lines for comparision as long as it meets the below criteria:
a) Same ETID, Cash Location, Currency
b) Transaction Sub type is same for all Activity(BUY/SELL)
Hi Team,
Can anyone look into on this?
Hi @Abhiram , I’m so sorry, but I am quite busy these days, and it looks like this can take quite some time to look into. I mean, just to remember what was done from the original thread is going to take long since that thread was quite huge.
May be what you can do is try to translate the new rule in the same format of what was done in the first thread. Once translated, it can become clearer how it can be integrated with the rest of the rules.
I would also suggest that you try to understand what was done in the first thread. Essentially the reason we got through the first thread was doing the translation, which then allowed us to compare and see what common conditions the rules had (that your main/parent if statement) and then to break them into sub conditions for their own particular rule (the multi if and else if inside the parent if statement). All the information there was to help you understand the approach and how the solution was reached, so that you can become self-sufficient if you have new rules to add. Otherwise, we could have just given you the answer.
4 Likes
Hi @bruno29a,
Thanks for replying.
Actually before what we use conditions were bit different compare to new challange which i posted above:
→ Because previously we just comparing wether any two rows become equal with respect of the 9 conditions which we had given in python.
→ currently our new challange was bit same but mostly we need to be focus on AMount column
→ I mean other things are same like
- ETID’s are same, cash location, currency, Transaction subtype was also be same
→ Here one new thing we need to do compare with if 1st row was compare with 2nd row ETID was same then check with Cash location, currency, transaction subtype and also Activity must be SETT/REIN for two rows but Amount of 1st row & 2nd row either both rows of amount may be it will be same or different anything . while comparing we need to sum the first two rows of amount (Assume 1st row Amount was 50 & 2nd row Amount was 80 their sum was 130 need to compare with all rows whether any other row of Amount was 130 is there or not if it there Example we compared for 1st and 2nd rows of data where as 30th row is Etid need to be different and amount was 130 and also Activity should be SETT/REIN if first 2 rows Activity was REIN then 30th row Activity should be SETT or if first 2 rows of Activity was SETT then 30th row should be REIN and also the 2 rows sum of amount like SUM(1) + sum(2) == sum (30) then output colud be as Cleared?(Yes/No) : Yes for 3 rows if not all rows should be No
→ here one Imp thing was we should assume only two rows was same May be if 5 rows also Same will be come in data . so for comparing there was no limit
Hi Team,
Any solution did u got???
Hi @bruno29a
Do you need anything reference?
Hello all,
Atleast can anyone has any doubts while doing in this case or not able to understand, please let me know.
→ whether it’s very difficult to do these type of tasks.
→ atleast let me know if you had any doubts.
or
i can understand that this task was able to do difficult
Facing An issue at:
previously we are comparing two rows each other where Row 1 == Row 2 if it equals then 2 rows data of cleared column was “Yes”. if it not then Row1 == Row3 again Row 1 == Row 4 in same manner
→ Now current task was
- Row 1 == Row 2 {Legal entity(1) == Legal entity(2), ETID(1) == ETID(2), CashLocation(1) == CashLocation(2) , Currency(1) == Currency(2), after that Activity should be (Activity(SETT/REIN)(1) == ACTIVITY(SETT/REIN)(2) definitly Activity(1) should be SETT then Activity(2) also SETT or Activity(1) was REIN then Activity(2) was REIN…
if it satisfies above then we need to go directly to 3rd row(Row 3) we need to check for either ROW1 == ROW3 (or) ROW2 == ROW3
At that time of comparision ((ROW1 == ROW3) If Legal Entity(1) != Legal Entity(3) we directly stop comparision and check with next Row If in case Legal Entity(1) == Legal Entity(3) , go and check ETID(1) == ETID(3) check for cashlocation, Currency, Activit. In between the comparision where ETID or Currency or Cashlocation was not same then directly comparision should be Stop. if it equal for all the columns of either Row2 == Row3 or Row1 == Row3 then we need to check for 4th Row.
And also while comparsion Row 1 with Row 3 or Row2 with Row 3 whether ETID(1) != ETID(3) then directly check the column(Amount) Example if Row 3 amount was 500, where Row 1 Amount was 270 & Row 2 Amount was 230 ( or )Row1 amount was 250 and Row 2 also 250 then Now need to sum Row1(Amount) + Row2(Amount) == Row3(Amount) and If Activity(Row1) was SETT & Activity(Row3) was must be REIN or acivity(Row1) was REIN then Activity(Row3) was must be SETT then only we need to go Column: Cleared(Yes/No) and change values for all 3 rows as “Yes”.
incase amount of Row 3 was not equal to Row1 + Row2 we can go to directly Row 4 and do comparision for Row 1 === Row 4 or Row 2 === Row 4 this was the process need to do every time.
you may get anotther doubt of if Row1 != Row2 then we can directly check with (Row1 == Row3)
the above explanation was for just 3 rows
You have provided a lot of information and several explanations. Unfortunately I was not able to fully grasp the structure and my impression is there are more complicated cases once you adapt them to a larger file.
From my experience in such cases you have to invest in a planning strategy and with KNIME it will involve a loop construct.
Maybe you could provide a larger sample with a full description of the rules also including some edge cases that might occur and will have to be dealt with.
Will the rows always be in the right order?
Will there be a ‘block’ that can identify a group you want to check or will there have to be searches across the whole data set? KNIME can do both.