Web Data scraping and select the cloumn with earliest date

[ppr (Peter)]

Give a try on

Assign Actvitiy
LHS: drFirstOldestRow | DataType: DataRow
RHS:
yourDataTableVar.asEnumerable.OrderBy(Function (x) CDate(x(Date).toString.Trim).Date).First()

[Gokul001 (Gokul Balaji)]

@Gokul001

(From row In Dt Order By DateTime.ParseExact(row.Item(“Date”).ToString, “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture) Ascending Select row).CopyToDataTable

Assign this in a datatable variable and pass that variable to For each row in datatable

Then loop through it

Regards
Gokul

[Chris_Bolin]

Welcome to UiPath and the forum! If you are using the Data Scraping activity, you will extract an “ExtractedDataTable”. You would in this case iterate through each row using a For Each Row in DataTable activity. Use this LINQ statement:

DateTime minDate = Convert.ToDateTime(((from DataRow dr in YourDtname.Rows orderby Convert.ToDateTime(dr[“DateColumn”]) descending select dr).FirstOrDefault()[“DateColumn”] )

[Ping_Pong]

Hi @ppr @Gokul001
Thank you for the reply

I have tried with this logic but loop is needed to match the company column "Wipro "(matching from Excel spreadsheet). If I get two or more same companies then I have to go for the Oldest date. Some cases have other companies with the oldest date.

Please suggest.
Thanks.

[ppr (Peter)]

@Ping_Pong

If I get two or more same companies then I have to go for the Oldest date. Some cases have other companies with the oldest date.

looks like you want to get for each company the oldest row.

This we can do with grouping the data

DTCompanyOldestRows | DataTable =

(From d in YourDataTableVar.AsEnumerable
Group d by k=d("Name").toString.Trim into grp=Group
Let dro = grp.OrderBy(Function (x) CDate(x("Date").toString.Trim).Date).First()
Select r = dro).CopyToDataTable

You will get back a datatable with the oldest row for each company

[Ping_Pong]

Hi @ppr

Thanks for the reply
but I am confused with the logic.
Could you please elaborate on how can I pass this activity?

Thanks.

[ppr (Peter)]

it is about LINQ

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operators Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start # All Operato…

Just us an assign activity

LeftSide: DTCompanyOldestRows - a variable of DataType: DataTable
RightSide:

(From d In YourDataTableVar.AsEnumerable
Group d By k=d("Name").toString.Trim Into grp=Group
Let dro = grp.OrderBy(Function (x) CDate(x("Date").toString.Trim)).First()
Select r = dro).CopyToDataTable

YourDataTableVar = use the VariableName of your extracted Datatable

[Ping_Pong]

Hi @ppr
I am facing compilation error in the activity, please share any steps to achieve this

Thank you.

[ppr (Peter)]

did check and updated it. surrounding " from

CDate(x("Date").toString

was corrected

[Ping_Pong]

Hi @Palaniyappan @Yoichi @kirankumar.mahanthi1

Please help me to achieve this.
Thank you

[ppr (Peter)]

@Ping_Pong

@ppr

did check and updated it. surrounding " from

was it working?

[Ping_Pong]

No Sir, @ppr

I am getting this error.
Please help
Thanks

[ppr (Peter)]

there is an empty value in your data for a date column value
can you check if we can filter out such rows when doing the processing

[Ping_Pong]

@ppr Sir,

How can I get only oldest date from the data table. So that I can match by creating another loop.

Please help
Thank you.

[ppr (Peter)]

@Ping_Pong

How can I get only oldest date from the data table. So that I can match by creating another loop.

please let us progessing by not repeating the case, but answering the open questions

@ppr

there is an empty value in your data for a date column value
can you check if we can filter out such rows when doing the processing

we were asking to check the data on rows with empty values on the date column (could also be the latest one only) and if it will harm, when we do filter out these records

A LINQ doing this filtering e.g. could look like this:

(From d In YourDataTableVar.AsEnumerable
Where not (isNothing(d("Date")) OrElse String.IsNullOrEmpty(d("Date").toString.Trim))
Group d By k=d("Name").toString.Trim Into grp=Group
Let dro = grp.OrderBy(Function (x) CDate(x("Date").toString.Trim)).First()
Select r = dro).CopyToDataTable

also ensure that the date formats are consistent and parseable with CDate. Otherwise we have to handle this also as well

[Ping_Pong]

Thank you sir I got the row with the Date

[Ping_Pong]

Hi @ppr sir,

Its throwing an error if the data table is null.

How can I move on if even the no data?

Please suggest

[ppr (Peter)]

following pattern can do it:

when we would expect an empty filter result we use the toList.
Depending on the List count we execute the CopyToDataTable or clone the origin datatable

just adopt it to your case and feel free to rename the variable drDuplicateOnly e.g. to drResult
drResult is of Datatype: List(Of DataRow)

[Ping_Pong]

Hi @ppr Sir,
Should I replace this code