Delete rows where 2 columns match

Help Activities
, , ,
#1

Hey everyone,

I have a datatable which I delete the rows where the rows “first quantity” and “second quantity” column values match.
But sometimes the excel is exported faulty and for examle,

first quantity value is 72 and second quantity column value is 72000, but actually this a column I want to delete.
So is there a way I can do something like; if second column quantity value includes first quantity columns value, remove that row from datatable?
Since “72000” includes “72” it could do the job for me.

(Also I cannot use for each row because there are 15000 rows so it takes alot of time)

I need tomething like :
(From d In dtMainData.AsEnumerable
Let k1=d(“first quantity.”).ToString
Let k2=d(“second quantity”).ToString
if k1.contains(k2)(delete row)

Something like this :slight_smile:

Thank you very much

#2

@jntrk
lets assume the faulty values are in only following pattern: 0,1 or more “0” are appended to a value then we could check following

retrieve the longest string length from first/second col
use for the check a rightpad

where k1.PadRight(maxLength,"0"c).equals(k2.PadRight(maxLength,"0"c))

about removal we can do just a filtering and using the result for further processing

a contains could be risky as
23 vs 23000 would match but also 23 vs 702300

#3

Hey @ppr ,

I havent really understood the job the solution you provided could you eleborate on it?

But the faulty values are always like following:
54 - 54000 or 54 - 5400,00

contains would be problematic I aggree, how would .startswith work?

#4

@jntrk
lets start with a try on:

Assign activity:
leftSide: dtFiltered (Datatype: Datatable)
right side:

(From d In dtMainData.AsEnumerable
Let k1=d("first quantity").ToString
Let k2=d("second quantity").ToString
Let m = {k1.Length, k2.Length}.Max()
Where Not k1.PadRight(m,"0"c).Equals(k2.PadRight(m,"0"c))
Select d).CopyToDataTable

handling empty filterresult can be done with this pattern:

grafik
grafik707×346 46.6 KB

sure we can do some miodifications e.g. starts with … But for this please give feedback on remaining open point:

is this the case?

#5

yes the case is like that and there is acutally 1 more case like “2” → “2.000,00” or “1.4” → “1.400,00” or “2.45” → “2.450,00” etc.

So cases I have in hand are the ones I wrote on top and 54 → 5400

and in your solution I get the datatable that includes these rows, but actualy I want to remove these rows from the original datatable.

Also thank you very much :slight_smile:

#6

@jntrk
Edited the LINQ as the Not for filtering out was ommited
can you please rerun the LINQ and check if the created datatable is in match with the expected output.
Thanks

following cases:
“2” → “2.000,00” or “1.4” → “1.400,00”
we will handle in a next iteration

#7

image
I am getting this error where does it expect it
Once I add “Not” before “Where” i start to get this error

EDIT: i made it as “where not” and it working now, could you explain the working procedure of this LINQ?

#8

@jntrk

Line1 - iterate over datatable, refer to looped row by d
Line2/3 - memorize the values from first/second qty
Line4 - retrieve the longest lenth from k1,k2
Line5 - filter the rows
Line6 - use the rows, passing the filter for copying to a datatable

#9

Thank you very much,

It outputs header names as cell values on some of the rows.

I havent used the code on duplicates, could it be because of it?

#10

not understood. can you please show screenshot / samples? Thanks

#11

its very confuse peter could you please share any workflow I am new in linq so here what is means of let,padright please help me out

#12

Padleft padright fills Up a String with a Char to a defined length

What is means tolookup in linq
#13

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