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.
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)