Column Expression - Create an If with List Contains

Hi Friends

I I’m migrating my Power Query code to knime.

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.

Does anyone know something similar?

Hi @Felipereis50 , you can check the Rule Engine node

Hi Bruno,

Do you know how I create a list in java language?
Is similar to IN

Can you help me with the formula?

I’m looking on the Internet

You can use IN() in the Rule Engine:

Results:
image

1 Like

Owww…
Ok.

I’ll try that Bruno!!

I get back to you.

I did it Bruno.

Thanks for the help my friend. :fist_right: :sparkles: :fist_left:

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 :slight_smile:

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

2 Likes

Hi Bruno

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.

Thanks for changing to the correct solution :slight_smile:

1 Like

Out of curiousity
@Felipereis50
any particular reason to replace power query with knime?

Hi Daniel,

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.

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.