Group the same serial no and select the oldest date

Learning Hub Academy Feedback
, ,
1

In the excel file i want to group all the same Serial No_ in one group and take the oldest Date in the output

Output should be like image

I am attaching the data here

Cubage Serial No_ Posting Date
3 SS-199796 21 January 2020 abc 22 January 2020
3 SS-199796 21 January 2020 abc 27 January 2020
3 SS-199796 21 January 2020 abc 23 January 2020
3 SS-199796 21 January 2020 abc 01 February 2020
6 UT-12345 15 February 2020 xyz 17 February 2020
6 UT-12345 15 February 2020 xyz 10 February 2020
6 UT-12345 15 February 2020 xyz 06 February 2020

Required Output data

Cubage Serial No_ Posting Date
3 SS-199796 21 January 2020 abc 22 January 2020
6 UT-12345 15 February 2020 xyz 06 February 2020

Please help

I am unable to find any solution for this problem

2

@Swara_Soni
Welcome to the forum
Find Quick Prototype done with LINQ showcasing your scenario in general
GroupBy_IDCol_TakeOldestDateRow.xaml (8.8 KB)

The LINQ can also be decomposed to more essential Activities and we can guide you on this

3

Thank you so much @ppr for the help

but when i applied the workflow it is showing the following error

image

i am unable to understand that why it is happening and how to explicitly define the data type.

Thanks again :slightly_smiling_face:

4

ensure this:
grafik

5

This condition is ok in the workflow but now the problem is the workflow is showing the error that
Assign: String was not recognized as a valid DateTime.

While using Build Data table activity the workflow is working fine but when i am fetching the data from my excel file it is showing the above mentioned error.

The same date format i.e. long date format i used in the excel

image

Please help @ppr

6

@Swara_Soni Can you first Check if the Date appears in that format, by using a For Each Row Loop on the Datatable and using a Message Box to show the Date Value, if it is different then you can use the format which appears in the Message Box.

7

Thanks @supermanPunch for the reply.
The date was in the format dd/MM/yyyy hh:mm:ss
image
and the error occured
Assign: The DateTime represented by the string is not supported in calendar System.Globalization.GregorianCalendar.

8

@Swara_Soni Actually it looks like it’s in this format :

MM/dd/yyyy HH:mm:ss

9

Thanks @supermanPunch it worked.

1 Like
10

find this decomposed approach here:
GroupBy_IDCol_TakeOldestDateRow_DecomposedLINQ.xaml (10.3 KB)

with this version you will get more debugging control and brings out the LINQ Blackbox to more transparency. You faced some date format issed and solved it with rewriting the format pattern. Such debugs / inspections are executable in this decomposed approach e.g with interacting with the Watch panel, intermediate panel.

Kindly note: the fomat pattern fix is not included just to be conform with the sample data.

Let us know on how more we can support you on your case

1 Like
11

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