How to identify multiple rules for the same row using if-else and join all results in the same row? Or other Idea

H friends,

I would like some help in solving a problem I have!
I’m sure it’s easy to understand. The explanation below will be a bit extensive but well explained.

How could I write code, or use a loop, or some other rule where I need to validate multiple rules for the same line, and if a rule is identified, concatenate the result of all the rules found for the same line?

I’ll explain step by step what I need…

I have this table with several columns.
image

Each row I need to analyze and identify issues in the database; however, I may have multiple errors for the same row. *

Example:
In the first row, if the code in the “CFOP” column is “6108” and the value in the “V.ST” column is greater than zero, then it’s an error (which I’ll call “Error 1”).
But also, in the same row, if the code in the “CFOP” column is “6108” and the value in the “V.ICMS” column is equal to zero, then it’s an error (which I’ll call “Error 2”).

Now imagine that I’m writing code in the “Column Expression” node, and I’m using multiple IF and ELSE IF statements.

Code

arrCFOP = arrayCreate(“6108”)
arrCFOP2 = arrayCreate(“5409”)

varVST = column(“V.ST”)
varVICM = column(“V.ICM”)
varVBC = column(“BC”)
varVTTPROD = column(“TT.PRODUC”)

if (arrayContains(arrCFOP, column(“CFOP”)) && varVST > 0)
{“Error number 1”}

else if
(arrayContains(arrCFOP, column(“CFOP”)) && varVICM == 0)
{“Error number 2”}

else if
(arrayContains(arrCFOP, column(“CFOP”)) && varVICM > 0 && varVST == 0)
{“No error-3”}

else if
(arrayContains(arrCFOP2, column(“CFOP”)) && varVBC > varVTTPROD)
{“Error number 4”}

else if
(arrayContains(arrCFOP2, column(“CFOP”)) && varVST == 0)
{“Error number 5”}

else if
(arrayContains(arrCFOP, column(“CFOP”)) && varVBC == 0)
{“Error number 6”}

else
{“@”}

The result unexpected is that the row-by-row iteration considers only the first true result found and moves on to the next row, and when it found the true value for the first row, it printes the result in the new column, not giving second chance for the next else if.

What I want!!!
I would like all the rules in the code to be considered in the first row, then after reads all the rules, goes to second row, so on…, and the result to be concatenated (output example futher)

The expected output would be like this: OR something else

Important consideration:
In production, I have a table with 300,000 rows, and I will have approximately 30 rules.

I need all 30 rules to be read for each row to identify all possible errors that may occur.

---------------------------------Ideas-----------------------------------------------
I had some ideas, but I’m not sure if they are the best:

1. Instead of creating all the rules in just one column in the “Column Expression,” I would create 30 columns, each with its own rule. I tried doing this, but I found it to be slow.
2. I thought about using some type of loop. For that, I would need to create a table with all the IF-ELSE codes, but I would have to use them as flow variables within the “Column Expression,” which might be complex to pass all the text. (Is a “recursive loop” ideal for this? I’ve never used one and don’t know how to use it; I still don’t understand the concept.)


3. I imagined that for each ELSE IF, I would store it in a variable and concatenate the result in the next variable, but I’m not sure if that would work.

Well, does anyone have a good idea? I’m open to learn anything.
Multiple results for same column.knwf (7.9 KB)

@Felipereis50 I might take a closer look later. For now I can point you to this approach where I combined several rules over multiple columns and collected them as booleans which I then aggregated again in collection columns.

In this example you could combine negative and positive rules. Might look somewhat complicated but could be a start

Other than that you might resort to a massive combination of loops and have the rules maybe not hard coded but in a meta file.

To speed things up you could try parallel processing or streaming.

Hi mlauber,

I’ll need to analyze your example workflow, as just from reading it, it seems it might not solve the issue.

Regarding your suggestion to use parallel processing or streaming, unfortunately, I’m not familiar with them. I even considered creating a table (table creator) with two columns: the first column would have an index, and the second column would contain the expression I created from the column expression node, where each row would be an IF statement. Then, I would create a group loop for each invoice to display only one row, followed by another group loop for the index table. After that, I would need to take the column from the table containing the IF code, transform it into a variable, and pass it into the column expression’s flow variable. I’d store the result in a variable and, in the next iteration, concatenate the first result with any additional results that may occur.

So, at the end of the entire iteration for the index table, it would move on to the next invoice, and so on.

Do you think this is a good approach? Do you think it’s possible?

Hi @Felipereis50 .
Something like that?
Multiple results for same column2.knwf (73.4 KB)

Br

1 Like

I can’t speak to efficiency, but my suggestion for simplicity would be to loop the data through a codified rule engine for each rule condition.

This workflow encapsulates your rules in a table, then feeds the table to the Rule Engine (Dictionary). It records the results, collates them, then joins them back into the main data stream. It seems cleaner than maintaining an if/then/else matrix.

**Based on your expression code, there was no rule 4. My run of your Invoice 2 resulted in triggering errors 3 and 5, not 4 and 5.

The No error column may not be needed, as you can simply take the missing values after the join and replace them with a fixed string of “No Error”.

Multiple results for same column_RuleEngine.knwf (26.9 KB)

1 Like

Hi @hmfa
Hi @jweiner

Wow, you two did an amazing job.

Analyzing the " jweiner" file, the idea and execution you did was exactly what I had in mind. Passing the rule/code in “text” format and then using a LOOP. The difference is that I imagined doing this using the “column expressions” itself.

Since you used the Rule Engine dictionary (I’ve never used it, and it was nice to see how it works), it’s easy to understand.

However, my rules might get a bit more complex, and I might have to use some variables to store values within the same IF statement, so I’ll have difficulty using the Rule Engine due to my limited knowledge of the language. (Remember, I’m not great at Java, and I’ve been learning from you all.)

I really liked the creativity, and I would certainly use your workflow model.
image

image



**Then, hmfa ** came up with his analysis, which I also found incredible. I believe the way he did it aligns more closely with what I need and what I feel comfortable with.

image

Interestingly, I also imagined doing something like this, where I store the result of the first IF in a variable and then concatenate the results found.

What I didn’t know is that I could use IF without needing ELSE.

So, I’d like to clarify something with you, @hmfa .

Could you explain what the symbol “+” in front of the name “rules” means?

Example:

if (arrayContains(arrCFOP, column("CFOP")) && varVST > 0)
{Rules=Rules+";Error number 1"}

What is the function of the “+”?

I’m definitely satisfied with both of your help. You both helped me perfectly. I’m very grateful.

I have searched for the symbol ‘+’, and it is a concatenation symbol for strings.

Thanks

1 Like

Hi @Felipereis50.
The @jweiner solution is better because you can have/maintain the rules outside the flow.

You may notice that all concatenated errors strings begin with a ; symbol and the last line of the script removes the first one.

Br