First record in each group ordered by Update date

Hi,

I want to retrieve the latest updated date for each order number. Here is a sample date

image

I want the output to look like below

image

Can you please help me? I tried linq but it is grouping the record by all the fields and not getting the latest status as one record for each order.

Thanks

Hi All,

I have seen your responses and solutions for lot of issues community members were experiencing. Can any of you please help me with this scenario?

@balupad14 @Palaniyappan @Lahiru.Fernando @arivu96

Hi @valathappan1,

use below query,

((From LineNo In dt.DefaultView.ToTable(True,"Order#").Select().ToList() Select (From row In dt.Select Where row("Order#").ToString=LineNo("Order#").ToString Select row).ToList(0)).ToList()).CopyToDatatable()

Regards,
Arivu :slight_smile:

1 Like

HI @valathappan1

Once you read this data into a datatable, Use a build datatable activity and build the same structure there as another datatable.

Now, Sort the data in the descending order of the date and order number.
Now, get the first one in the rows for each order number and add it to the next datatable.

At the end of the loop, you will be having a the output needed

1 Like

Hi @valathappan1,

Another alternative is to break down the problem into smaller pieces.

  1. Create the Final DataTable

  2. Get the list of unique Order#

  3. For Each activity contains Filter and Assign activities, to get the highest Update Date.

Filter Data Table activity and Add Data Row


Thanks All for your quck response. @Lahiru.Fernando I added order by update date descending clause after where in the query shared by @arivu96. I am getting the latest record for each group but while printing I have few order# getting converted as scientific numbers. PFB screenshot of the correct one in the left and incorrectly printed in the right. I verified that value is correct in the output datatable but it is getting distorted while writing to excel in write range. Any suggestions?

image