How to split a datatable into two based on condition

Help Studio
, ,
1

Hi!

I’ve a datatable to split based some condition.

If row value contains a word “admin” then split it.

For example:

Input DT

Name UserID Role Status
ABC 123 Developer, Administator, Limited admin Active
DEF 456 Robot, Developer, Administrator, Schedules_Admin Active
XYZ 789 Administrator, Folder admin Inactive
JKL 257 Developer Inactive

Output DT1

Name UserID Role Status
ABC 123 Administator, Limited admin Active
DEF 456 Administrator, Schedules_Admin Active
XYZ 789 Administrator, Folder admin Inactive

This DT contains all rows with keyword “admin” and excludes other roles

Output DT2

Name UserID Role Status
ABC 123 Developer Active
DEF 456 Robot, Developer Active
JKL 257 Developer Inactive
2

HI,

How about the following sample?

image
image989×1427 45.8 KB 

arrDr1 = dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({r("Name"),r("UserId"),String.Join(",",r("Role").ToString().Split(","c).Where(Function(s) s.ToLower.Contains("admin"))),r("Status")},False)).Where(Function(r) not String.IsNullOrEmpty(r("Role").ToString)).ToArray

arrDr2 = dt.AsEnumerable.Select(Function(r) dt.Clone.LoadDataRow({r("Name"),r("UserId"),String.Join(",",r("Role").ToString().Split(","c).Where(Function(s) not s.ToLower.Contains("admin"))),r("Status")},False)).Where(Function(r) not String.IsNullOrEmpty(r("Role").ToString)).ToArray

Sample20220909-2.zip (9.3 KB)

Regards,

3

Hi @Yoichi

Thanks mate! It worrked.

Just one question: Why is it required to convert it to array?

Cheers,
Abhi

4

Hi,

Because to prevent exception of no source row in CopyToDataTable method if result has no row.
And, in Else section, just use Clone method.

Regards,

5

Okay. Got it!

Thanks!

I’ve one more question from a diff thread. I was trying to check if a column value is present in other DT or not.

For example:

Arr_DT1Name = DT1.AsEnumerable().Select(Function (r) r.Field(of string) (“Name”).ToString.ToArray()

Arr_DT1Name – Array of string

Arr_DT2Name = DT2.AsEnumerable().Select(Function (r) r.Field(of string) (“Name”).ToString.ToArray()

Arr_DT2Name – Array of string

Now I want to check if all names from DT1 are present in DT2 and vice versa.

I’m trying to use:

NameChange1 = Arr_DT1Name.Except(Arr_DT2Name)

NameChange2 = Arr_DT2Name.Except(Arr_DT1Name)

This is giving me error as: Cannot assign from type 'System.Collections.Generic.IEnumerable to System.String in assign activity.

Am I missing anything in this mate?

6

Hi,

It seems type matter. Can you try to add .ToArray() as the following?

NameChange1 = Arr_DT1Name.Except(Arr_DT2Name).ToArray()

Regards,

7

Oh got it. Thanks a lot! Cheers :grinning:

8

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