Multiple conditions in WHERE clause is not working (LINQ)

Hi, after a data scrape into InvoiceAllDT, I want to select only those records where the amount is < 50000 and currency is CAD. Refer the ACME screenshot below.

I am trying the following LINQ and it returns all rows with amount < 50000 but has different currencies including CAD. So the 2nd condition is not working.

(From row In (InvoiceAllDT.DefaultView.ToTable(False,“Invoice Number”,“Vendor Tax ID”,“Invoice Item”,“Total”,“Date”)).AsEnumerable()
Where (CType(Split(Convert.ToString(row(“Total”))," ").ToArray(0),Double) < 50000 AndAlso Convert.ToString(row(“Total”)).Contains(“CAD”))
Select row).CopyToDataTable

Any guidance is much appreciated.

Regards, Udayan

@Udayan

Try this:

  newDT = DT.AsEnumerable().Where(Function(row) Cdbl(row(“Total”).ToString.Split(" ".TocharArray)(0)) < 50000 AND row(“Total”).ToString.Split(" ".TocharArray)(1).Contains("CAD")).CopyToDataTable
2 Likes

OK, it actually worked! I simply deleted the excel file before running now and the output is correctly filtered. I am not sure if the file was opening with old data… very strange because I checked the Last Modified timestamp after every run and the file was getting overwritten.

2 Likes

Thanks Lakshman for so prompt help, my original query worked too :slight_smile:

2 Likes

@Udayan

Great :+1:

1 Like

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