Hi all,
I have 2 datatable- DT1 and DT2.
I want to filter through a COLA in DT1 from a list of Keys present in COL_KEY in DT2.
The keyword can either be present or can contain a part of it.
LINQ query that I’m using is producing a lot of duplicate value or junk value.
I’m using
(From dt1 in DT1.AsEnumerable() from dt2 in DT2.AsEnumerable() where dt1(“COLA”).tostring().contains(dt2(“COL_KEY”).tostring()) Select dt1).toarray().copyToDatatable
Any Idea?
Hi @Ray_Sha1 ,
Could you try this instead?
(From dt1 in DT1.AsEnumerable() from dt2 in DT2.AsEnumerable() where dt1(“COLA”).tostring().contains(dt2(“COL_KEY”).tostring()) Select dt1).copyToDatatable
Kind Regards,
Ashwin A.K
Hi,
Thanks for the reply
This is giving me the whole Input list.
Hi @Ray ,
Could you try this instead?
Declare an Array of Strings for the keys.
Assign->
Dt2.AsEnumerable().Select(Function(s) s("COL_KEY").ToString).Distinct().ToArray()
After that, declare a List of DataRows for checking if the condition applied populates the variable with values(directly casting it to datatable will result in error)
Assign->
Dt1.AsEnumerable().Where(Function(w) arr_colKeys.Contains(w("COLA").ToString)).ToList()
CompareDataTables.xaml (6.6 KB)
Kind Regards,
Ashwin A.K
let us understand that you are interested on dt1 rows when dt1.ColA value is contained in any values from dt2.Col_Key
arrKeys | String() =
dt2.AsEnumerable().Select(Function(x) x("COL_KEY").ToString).Distinct().ToArray()
Result | List(Of DataRow) =
(From d In dt1.AsEnumerable()
Let v = d("ColA").toString
Where arrKeys.Any(Function (x) v.Contains(x))
Select r = d).toList
then based on the filter result we can copy it to a datatable or clone the origin one
check within an if acitvity
Result.Count > 0
Then: dtResult = Result.CopyToDataTable
Else: dtResult = dt1.Clone
Hi,
This takes only those values which are equals.
I must also check the value which “contains”
Where arrKeys.Any(Function (x) x.Contains(v))
its is looping over all arrKeys items and checks for a string contains against the COLA value
If any value from arrKey is containing the looped cola value then the dt1 looped row will be forwarded
Hi,
Even though we check for contain, the code is checking on the same values.
Yea, But it considering only the exact same values.
First line would check on item is equal to llo base
second line is similar to the LINQ Hello, Hallo is like arKeys and we get true for llo. A check on string contains base
Please elaborate more on this in relation to the immediate panel output from above
@ppr,
I get that too.
lets consider the values in COLA are:
Abc
xAbc
Def
xDef
and in COL_Key are:
ABC
Def
the final output must have:
Abc
xAbc
Def
xDef
that is the outcome I looking for.
then we just incorporate the case insensitive check within
(From d In dt1.AsEnumerable()
Let v = d("ColA").toString.toUpper
Where arrKeys.Any(Function (x) v.Contains(x.toUpper))
Select r = d).toList
The value obtained is
ABC
Def
I’ll verify with other sets
@Ray_Sha1
got it, issue was about the swapped check:
(From d In dt1.AsEnumerable()
Let v = d(“ColA”).toString.toUpper
Where arrKeys.Any(Function (x) v.Contains(x.toUpper))
Select r = d).toList
find starter help here:
ppr_LINQBox_Ray_Sha1.xaml (10.5 KB)
I’d tried this as well.
The whole Input table is coming up.
Hey @ppr ,
Can you help me with “doesn’t contain” condition?
trying another method
looks similar to your requirement:
sure, just share sample data, requirment description and expected output sample. Thanks
@ppr,
I got the desired value using activities. I will share the files in a while.