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
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?
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.”
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
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