Not getting expected output from Except function

Hi Everyone,

Need help in one scenario.

I have two datatables which might have rows more than 800k. I wanted to compare both the tables and take out the rows which were in DT1 and not in DT2 so I used

DT1.AsEnumerable().Except(DT2.AsEnumerable(),System.Data.DatarowComparer.Default).CopyToDatatable

which was running fine but as except function returns only UNIQUE values so now post the result is coming duplicate values are vanishing up But i need the complete data without duplicate removal.

Can anyone help with an alternative approach or solution for the problem.

Much appreciated!
Thanks

Hi,

Can you try the following expression?

dt1.AsEnumerable.Where(Function(r) dt2.AsEnumerable.All(function(r2) not DataRowComparer.Default.Equals(r,r2))).CopyToDataTable()

Regards,

HI @Yoichi,

Thanks for the quick response.

I will test and let you know if that is giving expected results !!

Thanks!

DT.AsEnumerable.Where(Function(r) Not DT2.AsEnumerable.Any(Function(r1) System.Data.DataRowComparer.Default.Equals(r,r1))).CopyToDataTable

Hi @Yoichi,

I tested this and it is giving the initial results correct but it is taking a lot of time to run over a large set of data.

Like with the expect function the results were generated in around 30 - 40 minutes but using the above query its 5 hours and the bot is still running.

Can you suggest some more efficient ways to get this because this is a lot time and is breaching my SLA.

Much appreciated for your help.

Thanks!

Hi @Gangadhar_Athili,

Thanks for you response and a solution.

Bot is taking more than expected time to give the results its around 3 hours using the query and the Bot is still running.

Can you also suggest some more efficient approach so that I can complete my processing within SLA.

Thanks!!

Hi,

If your data doesn’t contains tab (for example), can you try the following?

dt1 = dt1.AsEnumerable.OrderBy(Function(r) String.Join(chr(9),r.ItemArray)).CopyToDataTable
dt2 = dt2.AsEnumerable.OrderBy(Function(r) String.Join(chr(9),r.ItemArray)).CopyToDataTable

Dim idx1 As Int32 = dt1.Rows.Count-1
Dim idx2 As int32 = dt2.Rows.Count-1
While (idx1>=0 AndAlso idx2>=0)
    If (String.Join(chr(9),dt1.Rows(idx1).ItemArray)=String.Join(chr(9),dt2.Rows(idx2).ItemArray)  )
	dt1.Rows.RemoveAt(idx1)
	    idx1=idx1-1
    ElseIf (String.Join(chr(9),dt1.Rows(idx1).ItemArray)>String.Join(chr(9),dt2.Rows(idx2).ItemArray))
        idx1=idx1-1
	    Else
	    idx2=idx2-1
    End If	
End While

Main.xaml (7.6 KB)

Regards,

Thankyou for this response @Yoichi I will test this, for knowledge purpose can you please tell what exactly this code is doing

and why we are omitting thing like Tab you mentioned?

Thanks

Hi,

Because the above expression concatenate each items with tab (Chr(9)) for evaluating. If your data includes tab, for example, A[tab]B , C and A , B[tab]C both will be A[tab]B[tab]C and there is possibility we cannot evaluate their equality.

Regards,

Hi @Yoichi,

Thanks for the explanation.

I tried running the invoke code but it not returning me correct result

As -

Limited set of data the above code suggested is giving me only 1 row which is correct but when I am using the new invoke code approach that is giving me 16 rows which is not correct and my data does not have TAB.

Please suggest

Thanks!

HI,

Can you share your data , if possible? It’s no problem if dummy data which can be reproduced matter.

Regards,

Hi @Yoichi,

I am trying to prepare a dummy data but on the dummy data the invoke code is working fine though. It is not working on actual data where the LINQ queries you provided was working fine.

I am though still trying to prepare some data.

Thanks

1 Like

Hi @Yoichi,

I am trying the dataset but till now not got luck on that.

Do you have any other suggestions/approach as well on code part which I can implement and get more efficient results.

Really stuck.

Thanks!!