Datatable : removing blanks from particular column and removing dates other than current date

Hi,

I have a data table in the below format:

SlNo,TRANSACTION_ID,PNR_NO,OPERATOR_ID(Booking),Operator_ID(Cancellation),PG NAME,CLASS,REFUND_AMOUNT,WAITING_AUTO_CANCELLED,TRANSACTION_DATE,TDR_CAN,USER_ID,PRINCIPAL_USER_ID,CANCELLATION_ID

I need to delete all rows where Operator_ID(Cancellation) is blank. I need to delete all rows where TRANSACTION_DATE is not yesterday.

Kindly suggest how to do this using LINQ.

@shrayud

Use filter data table activity

image

1 Like

HI Pravin,
Thanks but Date is in time stamp format :

3/15/2021 6:58
3/14/2021 16:29
3/13/2021 9:16

I will keep 3/15/2021 6:58 and delete the rest

Yes…You can do that

Hi Pravin,

This is not working in case of dates.

I am getting output dataTable as blank.

First check if column TRANSACTION_DATE in your dt has DateTime value type. If not u can parse it like here:

Next use filter with remove for Operator_ID(Cancellation) is empty.

Use another filter activity with keep for:

TRANSACTION_DATE >= Today.AddDays(-1) AND
TRANSACTION_DATE < Today

@shrayud
Replace cancel_date filer with >= Now
or cancel_date <= now.adddays(-2)

@shrayud

we can use a LINQ statement. It allows us a good control on datatype and conversion handlings

Use an assign activity
Leftside: dtFiltered - Datatype: datatable
Right side:

(From d In YourDataTableVar.AsEnumerable
Where Not (IsNothing( d("Operator_ID(Cancellation)")) OrElse String.IsNullOrEmpty(d("Operator_ID(Cancellation)").toString.Trim))
Where Not CDate(d("TRANSACTION_DATE").toString.Trim).Date <> now.AddDays(-1).Date
Select d).CopyToDatatable

For a defensive handling of empty result we can apply following pattern:

3 Likes

I assume I can change that cdate to datetime.parseexact to show the pattern of the string if I know it? I would use that in my automation :slight_smile:

yes. This can be done and it gives a more deep level of control we often do this as well

1 Like

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