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
1 Like
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 Preuss)
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 Preuss)
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 Preuss)
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 Preuss)
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 Preuss)
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 Preuss)
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 Preuss)
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.