Filtering data-table based on Column from other Datatable

Help Studio
1

I have two datatables
First Datatable

image
image1839×307 18.9 KB

Second Datatable
image

Based on the second datatable column “No.”, i want to filter the first datatable (based on column “Customer Code”).

If they are euqal I want that whole row of First datatable except column “Customer” and from the second datatable I want only column “Qty” values.
and then will write them to an excel sheet
Thanks!

2

@Sami_Rajput

Use a join datatable

Then after join use a filter datatable and filter tge columns you dont need

Cheers

1 Like
3

Hi ,

You can follow the below steps:-

  1. Read both excel using read range activity. Store them into FirstDT and secondDT datatable variable.
  2. Remove column ‘Customer’ from FirstDT using ‘Remove Data Column’ activity.
  3. Remove columns except QTY and No. from secondDT using same above activity.
  4. Rename column ‘No.’ to Customer Code by doing :- secondDT.Columns(“No.”).ColumnName = “Customer Code”.
  5. Then use merge activity to get your final table.

Please mark this solve , if this above solution helps

cheers

4

Hi @Sami_Rajput

→ Use read range workbook activity to read the first excel and store in a datatable variable called dt1.
→ Use another read range workbook activity to read the second excel and store in a datatable variable called dt2.
→ Use assign activity to create a output datatable called dtResult.

- Assign -> dtResult = New System.data.datatable()

→ Use another assign activity to write the linq expression,

- Assign -> 
dtResult = (From row2 In dt2.AsEnumerable()
            Join row1 In dt1.AsEnumerable()
            On row2.Field(Of String)("No.") Equals row1.Field(Of String)("Customer Code")
            Select dt1.Clone().LoadDataRow({row1("Customer Code"), row1("Barcode"), row1("SUT Item Code"), row1("Description")}.Concat(row1.ItemArray.Skip(1 + dt1.Columns.IndexOf("Customer"))).ToArray(),
                                          row2.Field(Of Object)("Qty")).CopyToDataTable()

→ Use write range workbook activity to write the dtResult datatable to the output excel.

Hope it helps!!

5

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