Match and Remove

Help Studio
, , ,
1

I have a dataset

image
image940×613 159 KB

is there way to remove rows where Combined start date equals to TRS Combined start date? I want to avoid using each row loop as the dataset might have more than 100k rows

Any help would be greatly appreciated

2

Hi @ravindar.singh ,

  1. Read the excel data with Read range activity, which will give you the output the DataTable variable(say input_DT)
  2. Loop through the DT using For each row in datatable activity
  3. Inside the loop use a If condition to check if currentrow(“conditional start date”).Tostring = currentrow(“TRS conditional start date”).Tostring the inside if use Remove data row activity and pass currentrow as input which will remove that row

Regards,
Rajeswari

3

@Rajeswari24 thanks for your response. I was trying to find a different way as I don’t want to loop though each row because I might have more than 100k rows. I was trying to find a simpler way so I can avoid each loop activity.

4

@ravindar.singh can you upload the excel here

5

@jack.chan

Book1.xlsx (8.8 KB)

thanks

1 Like
6

do we need to remove this row?

combined start date is blank

image
image1075×648 143 KB

7

Book1.xlsx (8.9 KB)

With SQL I can cast data in empty row’s

8

Hi @ravindar.singh ,

In that case you can use the below LINQ query to filter the DT first

DataTable.AsEnumerable.Where(Function (x) x(“conditional start date”).tostring = x(“DRS conditional start date”).tostring).copyToDatatable

This will return you a new datatable. Now with the master datatable and matched rows datatable you can fetch only unmatched rows from master datatable using below LINQ query

dt_NonMatched_Data = In_DataTable1.AsEnumerable().Where(function(row) Not In_DataTable2.AsEnumerable().Select(functionr.Field(Of String)(In_DT2_ColName_To_Match.ToString)).Any(function(x) x = row.Field(Of String)(In_DT1_ColName_To_Match.ToString))).CopyToDataTable()

Regards,
Rajeswari

1 Like
9

try this, it doesnt use for each activity @ravindar.singh
test.xaml (7.9 KB)

image
image1215×647 29.8 KB

logic:

  1. create list of unique trs combined start date
    dt.AsEnumerable.where(Function(row) Not String.IsNullOrEmpty(row("TRS Combined Start Date").ToString)).AsEnumerable.Select(Function(row) row("TRS Combined Start Date").ToString).distinct().tolist()

  2. filter dt where trsList doesnt contain Combined Start Date

dt.AsEnumerable.Where(Function(row) Not listTRS.Contains(row("Combined Start Date").ToString)).CopyToDataTable

1 Like
10

@Rajeswari24 Thanks again for your response and help. I am kind of struggling I have attached .xmal file. Can you please help me out to fix it.

Main.xaml (9.9 KB)

really appreciate your help

11

Hi @ravindar.singh
Request you to provide accurate Input Excel and Expected output Excel. :slightly_smiling_face:

12

Hi @ravindar.singh,

After going through your xaml got to know that we should remove all the rows for which “TRS combined start date” is equal to “Combined start date” and it is not like a single row comparison for “Combined start date” and “TRS Combined start date”. In which case the code I have mentioned above will not work. My bad for that.

I have tried the same solution mentioned like @jack.chan and it worked.

Find this attached xaml for the same.
Main (1).xaml (10.4 KB)

Hope this clarifies your doubt.

Regards,
Rajeswari

1 Like
13

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