Ray_Sha1
(Ray Sha)
March 7, 2022, 5:54am
#1
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
Gokul001
(Gokul Balaji)
March 7, 2022, 6:09am
#3
Hi @Ray_Sha1
Welcome back to community
Have a look on the thread
Hey guys,
Complete rookie and new member here, so i would appreciate any help.
I have two data-tables and i want to compare 1 column in both tables. I would like to only keep the rows in the first data table if the cell in column 1 of data table 1 is not present anywhere in column one of data table 2. I would ideally like to do this with just the base UI path and no packages or coding as such. [Capture] Attached is an example of my inputs and the output i would like to get. I know the easiest…
Regards
Gokul
Ray_Sha1
(Ray Sha)
March 7, 2022, 6:28am
#4
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
1 Like
ppr
(Peter)
March 7, 2022, 9:07am
#6
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
1 Like
Ray_Sha1
(Ray Sha)
March 7, 2022, 9:34am
#7
Hi,
This takes only those values which are equals.
I must also check the value which “contains”
ppr
(Peter)
March 7, 2022, 9:41am
#9
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
1 Like
Ray_Sha1
(Ray Sha)
March 7, 2022, 9:48am
#10
Hi,
Even though we check for contain, the code is checking on the same values.
Ray_Sha1
(Ray Sha)
March 7, 2022, 9:49am
#11
Yea, But it considering only the exact same values.
ppr
(Peter)
March 7, 2022, 9:54am
#12
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
1 Like
Ray_Sha1
(Ray Sha)
March 7, 2022, 11:14am
#13
@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.
ppr
(Peter)
March 7, 2022, 11:16am
#14
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
Ray_Sha1
(Ray Sha)
March 7, 2022, 11:24am
#15
The value obtained is
ABC
Def
I’ll verify with other sets
ppr
(Peter)
March 7, 2022, 11:43am
#16
@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)
1 Like
Ray_Sha1
(Ray Sha)
March 7, 2022, 12:43pm
#17
I’d tried this as well.
The whole Input table is coming up.
Ray_Sha1
(Ray Sha)
March 7, 2022, 12:50pm
#18
Hey @ppr ,
Can you help me with “doesn’t contain” condition?
trying another method
ppr
(Peter)
March 7, 2022, 12:54pm
#19
looks similar to your requirement:
Ray_Sha1:
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
1 Like
ppr
(Peter)
March 7, 2022, 12:58pm
#20
sure, just share sample data, requirment description and expected output sample. Thanks
1 Like
Ray_Sha1
(Ray Sha)
March 7, 2022, 1:05pm
#21
@ppr ,
I got the desired value using activities. I will share the files in a while.