LINQ skip data while equal

[mz3bel (Mohssine HILAL)]

Hello,

I need to remove rows from a DT, whens rows equal “C” in column B. But remove all other rows equal to number “4” in column A, only the ones related to the row containing “C”, means row “4” in column A has “C” in column B.

As you see on the below picture, column A has some rows that are identical, I need to remove all of them (means all the identical ones), even if only one of them has “C” in column B, if none has “C”, i keep.

Multiple (as identical) or single in column A, if it has “C” in column B, should remove it.

I though about using something like this, the idea is close… :

dtData.asEnumerable.TakeWhile(Function (x) x(1).toString.Trim.ToUpper.Equals("C")).CopyToDataTable

Would apreciate your help

Cheers

[Yoichi]

Hi,

Is the above input data? What each color means?
If possible, can you share input and expected output separately?

Regards,

[mz3bel (Mohssine HILAL)]

Hi,

Thank you for your quick response.

The input is something like this :

Colmun A Column B

1234 Currently
1234 Not Currently
1234 Not Currently
1234 Not Currently
1234 Not Currently
6537 Not Currently
5332 Currently
5332 Currently

The output should be this :

Colmun A Column B

6537 Not Currently

When ever there’s the word Currently, the rows should be removed, even it’s only on a single duplicated rows. Hope this clarifies my query.

Thank you

[Yoichi]

Hi,

How about the following? Does this work for you?

Sample20221005-1.zip (8.7 KB)

Regards,

[mz3bel (Mohssine HILAL)]

Hello,

Sorry for late response. Thank you for the solution, it’s exactly what I’m looking for, but there’s something I don’t understand, the first part as filter by “Currently” works fine, but once i use the group function it doesn’t work, unless I’m using your example, I must have missed something, can you please clarify the group part, how does it work ? Thank you in advance.

[Yoichi]

HI,

The above expression makes groups which based columnA and filter out the number of rows is not 1. I might misunderstand your requirement. Can you share more your input and expected output?

Regards,

[mz3bel (Mohssine HILAL)]

Hi

I don’t understand what you mean by the number of rows is not 1 ?

Example :

Column A Column B

222222224 Currently
222222224 NOT Currently
222222224 NOT Currently
222222224 Currently
222222224 NOT Currently

555555552 NOT Currently
555555552 NOT Currently

666666631 Currently
666666631 Currently
666666631 Currently
666666631 Currently

666666647 NOT Currently
666666647 NOT Currently
666666647 NOT Currently
666666647 Currently
666666647 NOT Currently

666666671 NOT Currently

As you can see here, we have the same number “222222224” on multiple rows on column A, we should processes each row differently, but since one of the rows has the value “Currently” in column B (no matter if it’s only one or multiple, at least one containing the value “Currently”). The whole list of that same number “222222224” as rows should not be processed.

So all the ones, which not contains the value “Currently”, should be taken, as a result i should have something like this as an OUTPUT :

555555552 NOT Currently
555555552 NOT Currently

666666671 NOT Currently

Hopes this clarify my query, thank you again

[Yoichi]

HI,

Sorry i had misunderstanding your requirement. How about the following?

dtResult = dt.AsEnumerable.GroupBy(Function(r) r("Column A").ToString).Where(Function(g) not g.Any(Function(r) r("Column B").ToString ="Currently")).SelectMany(Function(g) g).CopyToDataTable

Sample20221005-1v2.zip (10.5 KB)

Regards,

[mz3bel (Mohssine HILAL)]

Hi @Yoichi

Thank you for you response. It worked fine for the few thousand rows, than at somepoint it start taking “Currently” instead, would you by any chance know what went wrong ? I mean it should work even if the file contains more than 5k rows ?

Regards,
Mohssine

[Yoichi]

HI,

There might be extra whitespace in the remaining data because the above expression strictly check “Currently”. Can you check this? If so, the following will work.

dtResult = dt.AsEnumerable.GroupBy(Function(r) r("Column A").ToString).Where(Function(g) not g.Any(Function(r) r("Column B").ToString.Trim ="Currently")).SelectMany(Function(g) g).CopyToDataTable

Regards,

[mz3bel (Mohssine HILAL)]

Hi @Yoichi

Thank you so much, it works perfectly.

Regards,
Mohssine