Generate Sequence Number based on Start Date

[former_member730756]

Good day.

I have a requirement where I am creating an iflow to generate csv file based on data received. Example:

One of the fields that I need to populate, called Sequence Number, should be determined based on the dates received..

What this means is, I can have multiple rows of data that have a start date i.e. Row 1: 22 Feb, Row 2: 21 Feb, Row 3: 20 Feb. Now based on these three dates located in different rows, the Segment Number of each row needs to reflect as Row 3: Sequence number = 1, Row 2: Sequence Number = 2, Row 1= Sequence Number = 3.

I have attached an image below of a sample CSV to show what I am trying to accomplish.

If anyone has an idea for the Groovy script to achieve this, it will be greatly appreciated.

Thanks.

[vadimklimov]

Hello Mohammed,

Let me provide a made-up example that will be a simplified version of what you have in the CSV, but which will address your question and will illustrate a possible solution.

Let's assume we have a way simpler row structure - it only consists of columns 'id', 'startDate' and 'sequenceNumber'. A key part here is not a number of properties contained in the row, but a field which value can be used as an identifier of the row. I illustrate an idea having a single key field - in more complex scenarios, it can be a compound key that would involve several fields - the example can be adjusted to cope with that.

Firstly, we define a Groovy class for a row - so that each row can be represented as that class's instance:

@Canonical
class Row {
    String id
    LocalDate startDate
    int sequenceNumber
}

To set the scene and prepare the example, I create an original list (named 'rows') that is going to contain representations of rows. In sake of simplicity, I hard-code its content here with some sample values - in your scenario, the list will be filled with row entries that you can from the source:

List<Row> rows = [
        new Row('Example_1', LocalDate.parse('2021-01-10')),
        new Row('Example_2', LocalDate.parse('2021-01-01')),
        new Row('Example_3', LocalDate.parse('2021-01-05')),
        new Row('Example_4', LocalDate.parse('2021-01-25')),
        new Row('Example_5', LocalDate.parse('2021-01-20'))
]

(note that I simplified date formatting here and I use ISO format - you might need to use an appropriate DateTimeFormatter to parse dates that you have in your scenario depending on format in which they are represented).

Now we arrive to the main subject - we will need two following steps:

1. Create a Map that will contain all keys (identifiers) of rows from the original list and dates that we are going to use in sorting exercise later. We don't need to copy the entire original list, as rows might contain a lot of other properties, and all together, this might increase memory footprint of the script, so we will keep it lightweight and only copy identifiers of rows (that we can use later for matching entries between an original list and copied and sorted connection) and dates that will be used in sorting. In my example, such a Map object is named 'idsSortedByDate'.
2. Sort the created map by a value (that is, a date - we want to get the collection sorted by date). In my example, I combined creation of a map and sorting of its entries by date.
3. Next, we iterate through entries of the original list (throw rows) and find indices of corresponding entries in a sorted map (matches between those two collections take place by the identifier of a row that is contained in both original list and sorted map). Index of the found entry in the sorted map is a reflection of its sequence number when being sorted by date. In your example, sequence numbers start from 1, so I added 1 to the resulting value that is assigned to a 'sequenceNumber' property (column of a row), since indexing in Java/Groovy collections starts from 0, and here we want computed sequence numbers to start from 1.

Map<String, LocalDate> idsSortedByDate = [rows.id, rows.startDate].transpose().collectEntries().sort { it.value }

rows.each { row ->
    row.sequenceNumber = idsSortedByDate.findIndexOf { it.key == row.id } + 1
}

And here we go - the original collection / list of rows (List 'rows') now got enriched with sequential numbers of its rows sorted by date ('startDate'), whereas the original sequence of rows in the list remains the same.

There is a variation to this solution: rather than using a map (of row identifiers and dates), we could have used a list of sortable entries in a copy of an original list (Groovy provides handy shortcuts for this, assuming corresponding class is annotated with @Sortable - see Groovy documentation).

One way or another, the main idea would remain the same: we take a subset of needed attributes to another collection, sort it by date, and then enrich the original list of rows with required sequence numbers by matching an original and sorted collections by attributes that can be used as row identifiers.

Regards,

Vadim

[tobias_breyer]

Hi Mohammed,

tag "SAP Cloud Platform Integration Suite" looks much more appropriate to me.

I asked moderators to move your question there, because with the lone tag "SAP Cloud Platform Workflow" that it has now, I don't think it will be found by the right experts.

Best regards,

Tobias