DT.SELECT not working properly with AND operator

Help
, ,
1

I have a DT I want to filter using the following code

db_in.Select(“[NAME] NOT IN (” +r1_1+ “) AND [VALUE] NOT IN (” + r1_2 +“)”).CopyToDataTable

the variables r1_1 and r1_2 contains strings of multiple values, so I’m not able to use Filter Data Table.

My issue is that I have a table that contains the values A, B, C and D (NAME) and Values 1,2,3 and 1, where the variable r1_1 contains A, B and C and r1_2 contains 1,2,3. By my reckoning, the code above should filter all values except for D 1. However, it filter D 1 as well, so it seems the AND operator isn’t really working, it’s just filtering based on both variables individually. Any suggestions?

2

@mickeymack
Can you Check the Part [value
the ] is Missing?

3

Yeah, that was a typo on my part :slight_smile: I’ve corrected it. The code runs and works fine, it just doesn’t do what I expected it to.

4

OK Let me Check give me some mins

1 Like
5

I do work more with LINQ as wit the select
But I would give a try on
db_in.Select(“[NAME] NOT IN ('” +r1_1+ “') AND [VALUE] NOT IN ('” + r1_2 +“')”).CopyToDataTable
so I just inclued ’ before " +r1_1 and on the end, similar to r1_2

If hint is not helping so just provide some sample data or the xaml and we can faster work on it, Thanks

6

That gives the error “Assign: Syntax error: Missing operand after ‘‘AP’’ operator.” I’ll provide the xaml and sample data.

7

Hi @mickeymack,

If r1_2 contains 1, 2, 3 then D 1 would be filtered out because 1 IS IN r1_2. Right?

8

input_data.xlsx (15.4 KB) rules.xlsx (9.9 KB) select_simple.xaml (10.1 KB)

These are my files. The rules file is read at the begining, and the input data is the data I want filtered according to the rules specified. Thanks so much for helping out.

9

Not according to my understanding since it needs to meet both criteria, i.e. be in A,B,C AND be in 1,2,3.

10

I was thinking the same …

11

As you said … need to meet both … and D 1 doesnt meet the second one … because 1 is r1_2 as @tmays said.

1 Like
12

But you’re saying “D” NOT IN “A,B,C” (which is true) and “1” NOT IN “1,2,3” (which is false) assuming this is your dt.

image

Sorry if I’m missing something.

13

Shouldn’t it keep it then? Since both statements are not true? Or does AND work in a different way than other programming languages?

14

AND works the same. Maybe you can use something like this.

NOT ([NAME] IN (" +r1_1+ “) AND [VALUE] IN (” + r1_2 +"))

15

The syntax is a bit of; I’ve changed it around to this:

db_in.Select(NOT “[Name] IN (” +r1_1+ “) AND [Value] IN (” + r1_2 +“)”).CopyToDataTable

which gives me a “Does not allow implicit conversion from boolean to string” error.

16

@mickeymack
There are some minor issues, but for sure solveable.
Unfortuately i do come to the same conclusion all rows and its values from input_data are contained in r1, r2 so returned DataTable is empty
A 1 is in ABCD, 1 is 123456789
B 2
C 3
D 1

Can you crosscheck from your end?

17

I’ve checked, and it seems to be working like this:
A 1 - A is in A,B,C and 1 is in 1,2,3, so A 1 is removed. OK
B 2 - B is in A,B,C and 2 is in 1,2,3, so B 2 is removed. OK
C 3 - C is in A,B,C and 3 is in 1,2,3, so C 3 is removed. OK
D 1 - D is not in A,B,C, and 1 is in 1,2,3, so for some reason I cannot get it is removed. NOT OK

If I change D 1 to D 4, it’s not removed.

18

Funny thing? If I change it to IN instead of NOT IN - it works as I’d expect it. I’m confused.

19

@mickeymack
thats the content of r1 “‘A’,‘B’,‘C’,‘D’,‘’,‘’,‘’,‘’,‘’”
thats the content of r2 “‘1’,‘2’,‘3’,‘4’,‘5’,‘6’,‘7’,‘8’,‘9’”

so D1 and D4 will be not returned as both are containees of the two list

20

I see that I’ve made an error in the file I attached. But if you remove D from the Name column in rules, you’ll see that it still removes D.