I want filter dataTable by use LINQ
Now my code as below.
DT_Detail.AsEnumerable().Where(Function(w) CInt(w(“APAPDT”)) >= [Start_APAPDT] and CInt(w(“APAPDT”)) <= [End_APAPDT]).CopyToDataTable
I want set value Start_APAPDT and value End_APAPDT by read file excel config (Column Start_APAPDT and column End_APAPDT )
config2.xlsx (8.9 KB)
Please guide me about it.
Hi
I think you are almost right with your expression
Dt = DT_Detail.AsEnumerable().Where(Function(w) Convert.ToInt32(w(“APAPDT”).ToString.Trim) >= Start_APAPDT and Convert.ToInt32(w(“APAPDT”).ToString.Trim) <= End_APAPDT).CopyToDataTable
Cheers @fairymemay
I want filter column PAID_DATE too.
How to edit code ?
Now I use code as below.
DT_Detail.AsEnumerable().Where(Function(w) CInt(w("PAID_DATE")) >= [ValueToCompareWith] And CInt(w("PAID_DATE")) <= [ValueToCompareWith2]).CopyToDataTable
But now error as below.
Assign: Conversion from string “” to type ‘Integer’ is not valid.
How to solve it ? ( some row have blank data)
It looks like you are already filtering PAID_DATE, may I know still what you are looking for. Also, try the below to avoid that error
DT_Detail.AsEnumerable().Where(Function(w) CInt(w(“PAID_DATE”).ToString) >= [ValueToCompareWith] And CInt(w(“PAID_DATE”).ToString) <= [ValueToCompareWith2]).CopyToDataTable
@ushu I want filter PAID_DATE and APAPDT.
But In PAID_DATE some row have blank.
And show error as below.
Assign: Conversion from string “” to type ‘Integer’ is not valid.
It’s same as above
Just include a AND operator or use a PR operator based on the condition you need
Say for example if you want AND to be used in the same expression including Paid_Dt
Then the expression would be like this
Dt = DT_Detail.AsEnumerable().Where(Function(w) Convert.ToInt32(w(“APAPDT”).ToString.Trim) >= Start_APAPDT AND Convert.ToInt32(w(“APAPDT”).ToString.Trim) <= End_APAPDT AND
Convert.ToInt32(w(“PAID_DATE”).ToString.Trim) <= PAID_DATE).CopyToDataTable
Cheers @fairymemay
@Palaniyappan But In PAID_DATE some row have blank.
And show error as below.
Assign: Conversion from string “” to type ‘Integer’ is not valid.
Fine
In that case use the below expression
Dt = DT_Detail.AsEnumerable().Where(Function(w) Convert.ToInt32(w(“APAPDT”).ToString.Trim) >= Start_APAPDT AND Convert.ToInt32(w(“APAPDT”).ToString.Trim) <= End_APAPDT AND
If(String.IsNullOrEmpty(w(“PAID_DATE”).ToString.Trim), False, Convert.ToInt32(w(“PAID_DATE”).ToString.Trim) <= PAID_DATE)
).CopyToDataTable()
You can change the above false as true if you still want to continue if that Paid_Date cell has empty value
Now as we have mentioned false it won’t filter the other column if Paid_Date has null valuen in it
Cheers @fairymemay
@Palaniyappan I have last question.
If I want read value Start_APAPDT, End_APAPDT from file config.
How to edit code?
Please guide me more for solve it.
Fine
Let’s take like you have datatable read from excel named dt
If you want to get only those two column before or after filtering it out
Then use this expression in assign activity to get those two specific columns alone as a datatable
Dt = Dt.DefaultView.ToTable(False, “Start_APAPDT”, “End_APAPDT”)
Or
If you just want to read the value of two columns without separating them
Then use a for each row activity and pass dt as input and inside the loop u can use the value by mentioning as CurrentRow(“yourcolumnname”).ToString
Cheers @fairymemay
@Palaniyappan I use code follow your suggest.
But I don’t know how to pass in below expression.
Dt = DT_Detail.AsEnumerable().Where(Function(w) Convert.ToInt32(w(“APAPDT”).ToString.Trim) >= Start_APAPDT AND Convert.ToInt32(w(“APAPDT”).ToString.Trim) <= End_APAPDT AND
If(String.IsNullOrEmpty(w(“PAID_DATE”).ToString.Trim), False, Convert.ToInt32(w(“PAID_DATE”).ToString.Trim) <= PAID_DATE)
).CopyToDataTable()
Please guide me more about it.
Fine first let’s filter the datatable with above expression and get the output with the variable named dt as mentioned above
Then use the DefaultView method to get only the two columns you want
I think that should be fine
Cheers @fairymemay
@Palaniyappan No.
first , I want to read file config and pass data for filter datatable.
Please guide me more.
I think the output you expect is going to be same in both the approach
The reason why I suggested to filter first is you have used three columns for filteration
But finally you want only two out of those
So if you get two columns in the very beginning stage then we won’t be able to filter it out with Paid_Date
That’s why first let’s filter out with three columns and then get those two columns alone as a separate datatable
Cheers @fairymemay
Why do you want to use LINQ?
It’s not faster. Just use Filter Datatable.
@Palaniyappan File config as below.
File input as below.
I want output as below.
Uploading: image.png…
Then it wasn’t configured properly.
ashokkarale
Anil_G
Yoichi
yangyq10
postwick
chandreshsinh.jadeja
aravindbalineni123
Parvathy
aya
PRASHANT_GABHANE