Need to filter from the line from the datatable

Help Activities
, , , , , , , , ,
1

My input data table is

Start date. End date. Value
1/1/2020. 1/31/2020. 245

1/1/2021. 1/31/2021. 345

1/1/2022. 1/31/2022. 346

1/2/2023. 1/31/2023. 456

And my second input is
Start date. End date. Value
1/1/2021. 1/31/2021. 345

I am comparing first and second input
And my second input falls on second line.so I need to filter from second line.so my output should be.

Start date. End date. Value

1/1/2021. 1/31/2021. 345

1/1/2022. 1/31/2022. 346

1/2/2023. 1/31/2023. 456

2

@sruthesanju

  • Read Range (Input DataTable1)
  • Read Range (Input DataTable2)
  • Assign (Create DataView for DataTable1)
    Expression: dt1View = New DataView(dt1)
  • Assign (Set filter expression for DataView)
    Expression: dt1View.RowFilter = “Start date >= ‘1/1/2021’ AND End date <= ‘1/31/2021’”
  • Assign (Create filtered DataTable)
    Expression: dtResult = dt1View.ToTable()
  • Write Range (Output filtered DataTable)
3

Hi @sruthesanju

Try this query:

filteredDataTable = (From row In dtInput1.AsEnumerable()
                     Where DateTime.Parse(row("Start date").ToString()) >= DateTime.Parse(dtInput2.Rows(0)("Start date").ToString())
                     Select row).CopyToDataTable()

Hope it helps!!

4

@sruthesanju

FilteredDt1 = (From row1 In Dt1.AsEnumerable()
               Where Not Dt2.AsEnumerable().Any(Function(row2) 
                                                    row1.Field(Of DateTime)("Start date") = row2.Field(Of DateTime)("Start date") AndAlso 
                                                    row1.Field(Of DateTime)("End date") = row2.Field(Of DateTime)("End date") AndAlso 
                                                    row1.Field(Of Integer)("Value") = row2.Field(Of Integer)("Value")
                                                  )
               Select row1).CopyToDataTable()

Please pass the datatype as per your excel if your data is in String format give it as string or it is in Datetime please keep as it is
If integer raises an error change it to double

5

Hi @sruthesanju

=> Build Data Table
image
Output-> dtInput1
=> Build Data Table
image
Output-> dtInput2
=> Use below syntax in Assign:

Assign-> filteredDataTable = (From row In dtInput1.AsEnumerable()
                     Where DateTime.Parse(row("Start date").ToString()) >= DateTime.Parse(dtInput2.Rows(0)("Start date").ToString())
                     Select row).CopyToDataTable()

filteredDataTable is of DataType System.Data.DataTable

Output:

image
image963×707 32.1 KB

Hope it helps!!

6

@sruthesanju

I believe you need this

dt1.AsEnumerable.Except(dt2.AsEnumerable).CopyToDatatable

cheers

7

Hey @sruthesanju
Below is vb.net code to use for your case:

Dim matched As Boolean = False
Dim outputDataTable As DataTable = dt1.Clone()

For Each row1 As DataRow In dt1.Rows
    If Not matched Then
        For Each row2 As DataRow In dt2.Rows
            If row1("Start date").ToString().Trim() = row2("Start date").ToString().Trim() AndAlso row1("End date").ToString().Trim() = row2("End date").ToString().Trim() AndAlso row1("Value").ToString().Trim() = row2("Value").ToString().Trim() Then
                matched = True
                Exit For
            End If
        Next
    End If
    
    If matched Then
        outputDataTable.ImportRow(row1)
    End If
Next

outputDT = outputDataTable

image
image416×592 12.3 KB

Project:
BlankProcess116.zip (3.1 KB)