Using flow variable in rule-based filter

Hi,
I’ve created a variable that captures a list of comma separated ID from string input, I want to use the comma separated list rule-based filter like this
$Customer_Id$ IN ($${SvCustomer_Code}$$) =>TRUE

The filter returns not data even though the id are in there. I check the value in the variable and its fine, even tested it an it works. Can anyone advice what i’m doing wrong. thanks

Hi @adjrock,

could you share a small example workflow so that we can better help you?

Best regards,

Paul

2 Likes

Test input.knwf (12.9 KB)

1 Like

Hi @adjrock

The syntax of your example is not the expected by the “IN” command. It should be the following:

The operator expects to have the values as parameters, not as a string of concatenated values to search IN.

The solution in your case would be to use the “LIKE” operator but for that you need first to add "*" and "*" at the beginning and at the end of the content of your $Customer_Id$ values. Somehow, it should be like this:

where the column $*_Customer_Id_*$ has been created using the -String Manipulation- node, as follows:

Other solutions exist and as said by @goodvirus, if you post a bit of your data in text format or a minimalist workflow, it would help us to help you too.

Best

Ael

2 Likes

Not sure I understand, so are variables incompatible to the IN operator, but LIKE operator can handle variables? I’ve posted my knwf in previous comment, together with example data, let me know if that’s not accessible

Thanks for posting the workflow.

Not really, it is not a problem with incompatibility between variables and the IN operator. The IN operator can handle string variables, but every item to search should be in a separated string variable, not in a concatenated string variable. For instance, this would be correct:

In other words, the IN operator is equivalent to this:

$Customer_Id$ = $$Variable_1$$ =>TRUE
$Customer_Id$ = $$Variable_2$$ =>TRUE
$Customer_Id$ = $$Variable_3$$ =>TRUE

or alternatively:

($Customer_Id$ = $$Variable_1$$) OR ($Customer_Id$ = $$Variable_2$$) OR ($Customer_Id$ = $$Variable_3$$) =>TRUE

but makes you life easier with a shorter syntax :wink:

The use of the LIKE operator as suggested before is just a trick to achieve what you need to do when using concatenated tokens separated by “comas” in a same string variable.

I’ll work from your workflow and come back to you soon to provide a possible solution :slight_smile:

Best

Ael

1 Like

@adjrock please find below the solution I suggested above:

From your variable and table data:

image

image

image

Test input LIKE solution.knwf (33.8 KB)

Hope it helps.

Best

Ael

3 Likes

I expected this to work with a string array as argument for the IN operator, but the Rule-Based Row Filter doesn’t accept string array variables as input. Strange.

If only exact matches are desired, a Reference Row Filter will work as well. Simply transform the input string into a column instead. The Cell Splitter insists on changing the type to double, so we need to manually cast back to a formatted string with a Java Snippet (number to string insists on using floating point).

//the zero forces zero digits after the decimal point, so it's a normal "integer"
out_values = String.format("%.0f", c_values);


flow variable in rule-based filter.knwf (36.6 KB)

2 Likes

Hi @adjrock , both @aworker 's and @Thyme 's are valid solutions, and quite innovative.

Going back to your original idea, the similar concept can be done by converting your customer_code input into an array, and then check if your value exist in the array via the arrayContains() function from the Column Expressions.

Workflow looks like this (just one more node added):
image

Code is like this:

If the value exists, it will return “True” else it returns “False”, and I save this result in a new column called “keep”.
image

Them simply filter based on the column “keep”.

Result:
image

Here’s the workflow: Test input_Bruno.knwf (14.4 KB)

3 Likes

Thanks all, while the wild card search solution is good, I’m a bit apprehensive when it comes to reliable matching as I need exact selection. @bruno29a the array contain function is interesting and I tried it, I’m guessing it will only return True if its exact match or is it that same as the wildcard search?

Hi @adjrock , it checks for exact match. Isn’t it what you wanted? Please let me know if you wanted to do wildcard.

Hi @adjrock

It is true that a solution based on wildcards “*” may lead to a not exact matching in some specific cases. However, if your comma separated IDs are always of same length, this definitely should not be the case in the solution I posted.

If they are not always of same length and hence one ID might be contained into another one as a subsequence of numbers, then the easy and efficient solution is to include the “,” (“comma”) separators in the LIKE query as follows: join( "*,", $Cust_ID$,",*")

The only added thing to do is to make sure that your concatenated string starts and finishes by “,” separators too, to be coherent with this LIKE search version based on comma terminators.

To summarize, the rule-base solution is absolutely valid with no possible mismatching error if coded as explained above. I’m adding here for completeness the modified solution based on the -rule-based row filter- node:

Test input LIKE solution with commas.knwf (41.4 KB)

As a feedback for the forum, it would be nice if you could compare the solutions proposed here, their results (whether they are the same) and eventually their efficiency when using them on real data (maybe huge amount of IDs). This can be monitored using the -Timer Info- node:

From experience, I have seen very different time performances between the nodes internally coded in Java (as it is the -rule-based row filter- node) and those for instance coded in JavaScript. It would be nice to see which of the above solution is less time consuming whilst equivalent in the results. I have not a priori idea of which one is more efficient. All of them may have their advantages and disadvantages though :wink:

Hope all this helps.

Best

Ael

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