Linq how to exclude OrderID from a other Datatable

Help Studio
,
1

Hi,

I have a challenge, the report contains orders with a value Rate > 47. However, there are also lines where this value is not available.

In the result, I only want to have orders that do not have a line with a value > 47.

How can I exclude the orders contained in DtExclude via Linq?

DtExclude

(From d In dtInput.AsEnumerable
Where d("Rate").ToString > "47"
Where Convert.ToDateTime(d("Date").ToString) <= DateTime.Today
Select d).CopyToDataTable

DtOutput

(From d In dtInput.AsEnumerable
Where d("Rate").ToString <= "47"
Where Convert.ToDateTime(d("Date").ToString) <= DateTime.Today
Select d).CopyToDataTable

Order 756422246590 i don’t want to have in DtOutput

Linq_Filter_Report.zip (9,1 KB)

2

Hi @raiko.m ,

When comparing numbers, we have to convert them to integers/double first.

From d In dtInput.AsEnumerable
Where CInt(d("Rate").ToString) <= 47
Where Convert.ToDateTime(d("Date").ToString) <= DateTime.Today
Select d).CopyToDataTable()

Is this producing the result that you were looking for?

Kind Regards,
Ashwin A.K

3

Unfortunately not, in DtExclude a list with order numbers - I can’t find a solution so that these OrderID are not included.

In my example i have also empty values for this Rate, and then i get a error “Assign ‘DtExclude’: Conversion from string “” to type ‘Integer’ is not valid.”

grafik

4

Hi @raiko.m ,

Could you please provide an example of the expected output?
I can help you from there.

Kind Regards,
Ashwin A.K

5

Hi @ashwin.ashok,

the Source Data i have shared in the Start Post.

Expected result

Order	Date	ANR	Rate
756422246546	15.09.2022	40-19-7469	
756422246546	15.09.2022	40-19-7495	
756422246546	15.09.2022	40-19-7471
6

Hi,

How about using DtExclude as the following?

DtOutput =  (From d In dtInput.AsEnumerable
Where  Not DtExclude.AsEnumerable.Any(Function(r) r("Order").ToString=d("Order").ToString)
Where Convert.ToDateTime(d("Date").ToString) <= DateTime.Today
Select d).CopyToDataTable

And as @ashwin.ashok mentioned, perhaps you should evaluate value as numeric as the following.

dtExcluse = (From d In dtInput.AsEnumerable
Where Int32.TryParse(d("Rate").ToString,New Int32) AndAlso Int32.Parse(d("Rate").ToString) > 47
Where Convert.ToDateTime(d("Date").ToString) <= DateTime.Today
Select d).CopyToDataTable

Regards,

1 Like
7

Before use Linq code… you can filter datatable to remove rows which has the Rate Blank value.

8

@Yoichi many thanks for your Solution

Linq_Filter_Report.zip (14,1 KB)

In Case if ‘Rate’ = IsNullOrEmpty i would be use the integer value = 0

# Rate NULL is a exception (don't output the Row)
Where Int32.TryParse(d("Rate").ToString,New Int32) AndAlso Int32.Parse(d("Rate").ToString) > 47

# Rate = NULL use int32 = 0

	Let v = d("Rate")
	Let chk1 = Not ( isNothing(v) OrElse String.IsNullOrEmpty(v.ToString.Trim))
	Let chk2 = If(chk1, Double.TryParse(v.ToString, Nothing), False)
	Let x = If(chk2, Double.Parse(v.ToString()), 0)
Where x <= 47

@ashwin.ashok thanks for your Support

1 Like
10

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