I have an “if” formula with an “and”. But in this second condition I do it based on a “List” using a power query function called “List.Contains” and at the end of this function I inform which column will be checked.
The if is like this:
if [#“Column1”] = “No” and List.Contains({“R5”,“R6”},[#“Column2”]) then “A”
else
if [#“Column1”] = “No” and List.Contains({“5405”,“5909”,“6915”,“6923”,“5102”},["Column3]) then “A”
else
if [#“Column1”] = “No” and Text.Contains(["Column4],“Y”) then “A”
else “B”
The first condition of the If is easy to understand, however, when it goes to the second condition, when informing the values of the list, it is like an “Or” , where the rule will analyze the first condition with the second.
For example:
Column 1 = “No” and Column 2 = “R5” or try
Column 1 = “No” and Column 2 = “R6” and so on…
Without a similar function, I would have to create several “If’s” to get the same result.
Any reason why you are using “LIKE” instead of “=”? You’d use LIKE if you were doing a wildcard match. In your case you are not doing any wildcard match, so using “=” is enough (and probably faster).
Also, you should mark the post that gave you the answer on how to achieve what you are trying to do, as Solution, not your workflow. Otherwise, everyone would be marking his own work as Solution, which does not make sense
EDIT: You can also write the rule as a one line:
$TemPartilha?$ = "Não" AND ($Cod.Mov.$ IN ("R5", "R6") OR $CFOP$ IN ("5405", "5102", "5909", "6923", "6915") OR $Serie$ LIKE "*Y*") => "Desconsiderar"
TRUE => "Considerar"
But it’s probably clearer with 3 lines. Just an alternative I wanted to share
I used Like because I don’t know much about the language. But I will change the same as your explanation. Thank you so much again and for teaching me the correct way. I also changed it to the correct solution.
No problem @Felipereis50 , happy to help. You actually did very good considering you don’t know much about the language. Keep practising and having fun with the Rule Engine and Knime.
I found Knime to be faster and the step structure more organized.
Especially when transferring the file to another person.
Overall, I’m finding it much more interesting.
