How to filter Datatable on the column?

Help
1

Hi All,

I have two big datatables. Both the datatables contains column “NotificationNo”
I want to filter second datatable -.
Condition → Only Same Notifications are acceptable. So Different notifications are not needed.

Note : I cannot use for each row loop as it will take too much time for processing.

2

@Paresh

Can you please explain in detail for better Understanding.

Regards,
Mahesh

3

Hello,

in order to filter datatable, you can use select() method of datatable like as below

dt.select(“NotificationNo = ‘123’”).

Cheers,
Pankaj

4

Dear Mahesh,

I have two datatables. DT1 and DT2

DT1 as below
NotificationNo Validity Status
123 Yes Open
456 No Closed
789 Yes Open

DT2 as below
notificationNo ID
456 23
658 34

Then,requirement is to filter on datatable DT2 which will return me row of 456. As it is the only notification no which matches with notification no of DT1.
I am not finding an way to filter like this.

5

hello paresh,

then join DT1 and DT2 data tables and based on notification number you can filter on DT1.The result will be array of data rows then loop through that

Thanks

6

Dear @cheersrpa,

I don’t have much coding experience on dotnet.
Can you please provide me example of syntax.

7

hello paresh

you can refer below url and modify that code according to your requirement.

Take one assign activity…create an variable of type array of data rows (let say arr_result) and in right side put your query

Thanks and Regards

1 Like
8

@Paresh

dtFiltered = (From p In dt1.Select
      Select If((From q In dt2.Select
            Where Convert.ToString(p("NotificationNo")).Equals(Convert.ToString(q("NotificationNo")))
           Select q).ToArray.Count>0,(From q In dt2.Select
            Where Convert.ToString(p("NotificationNo")).Equals(Convert.ToString(q("NotificationNo")))
           Select q).ToArray.CopyToDataTable,New DataTable)).ToList.SelectMany(Function(x) x.Select).ToArray.CopyToDataTable.DefaultView.ToTable(True,(From p In dt2.Columns.Cast(Of System.Data.DataColumn) Select Convert.ToString(p.ColumnName)).ToArray)

Better you take first Distinct Values from dt1
dtFiltered Will be your Output.
Try this Query. I have not tested the Code :grinning:

Regards,
Mahesh

9

Hi @MAHESH1,

It worked but returning me same row for 3 times. I want it only single time.

Output appearing like this.
NotificationNo ID
456 23
456 23
456 23

10

@Paresh

If your dt1 Contains 456 three times then it will return 3 times. that’s why I said take distinct values from dt1.

Other Wise I made Some Changes in the Code. Take the Updated One and try it Once.

Regards
Mahesh

Regards,
Mahesh

11

Hey @MAHESH1

Worked Perfectly! No I was using distinct values only but it was giving same row for three times. After using your updated code i got the required result.

Many thanks! :slight_smile:

12

hi @MAHESH1,

Just one more query. Now it is giving me output as all rows of DT2 which has no common notification Numbers. :frowning:

Please help

13

@Paresh

Sorry I am not getting, How it’s possible, You post your two Excel Sheets which you are using.

Regards,
Mahesh

14

Hi Mahesh,

PFA XAML File. TestDT.xaml (14.8 KB)

15

@Paresh

Use this Query

dtFiltered=If((From p In dt1.Select
      Select If((From q In dt2.Select
            Where Convert.ToString(p("Notification")).Equals(Convert.ToString(q("Notification")))
           Select q).ToArray.Count>0,(From q In dt2.Select
            Where Convert.ToString(p("Notification")).Equals(Convert.ToString(q("Notification")))
           Select q).ToArray.CopyToDataTable,New DataTable)).ToList.SelectMany(Function(x) x.Select).ToArray.Count>0,(From p In dt1.Select
      Select If((From q In dt2.Select
            Where Convert.ToString(p("Notification")).Equals(Convert.ToString(q("Notification")))
           Select q).ToArray.Count>0,(From q In dt2.Select
            Where Convert.ToString(p("Notification")).Equals(Convert.ToString(q("Notification")))
           Select q).ToArray.CopyToDataTable,New DataTable)).ToList.SelectMany(Function(x) x.Select).ToArray.CopyToDataTable.DefaultView.ToTable(True,(From p In dt2.Columns.Cast(Of System.Data.DataColumn) Select Convert.ToString(p.ColumnName)).ToArray),New DataTable)

Regards,
Mahesh

16

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