Forum Discussion

adam_gataev's avatar
adam_gataev
New Contributor II
2 years ago
Solved

Get MIN (oldest) or MAX (most recent) date from array of dates

Hi,

I first do an SQL Server - Execute and get this weird {_snaptype_localdatetime: "..."} for the CreationDate and LastUpdate fields. Then, using a Mapper snap I do toString() to just get the date fields in string form. 

Then, I do a Group By on some fields and add another Mapper snap where I have this:

Here, I am trying to get the MAX and MIN dates from the array.

This is what the whole pipeline looks like:

How can I get the oldest and the most recent dates from the array?

Thank you very much in advance!

Kind regards,

Adam

Expression Language and Expression Libraries
  • koryknick's avatar
    koryknick
    2 years ago

    adam_gataev - since you have it in an array, you can use the Array.reduce() method to get the min or max value out of the array.  It would look like this:

    $dateArray.reduce ((accum,cur)=> accum == null || accum > cur ? cur : accum, null)

    Note that this also uses the ternary operator for the if/then/else condition to determine which value is greater when comparing  the array elements.  One nice feature of this method is that it doesn't matter what the data type of the values are, as long as they are comparable.

    There are other ways you could do this, but this was my initial thought.  If you're interested, I've attached an expression library that I created a while back and share with many of my clients as it has many common functions that have been requested.  To use the expr lib, just include it into the pipeline properties, then your expressions to get the min/max values just use the string.min() and string.max() methods.  Note the use of the spread operator to expand the array to individual values since the expr lib method is expecting a list of values rather than an array.

    Hope this helps!

3 Replies

  • koryknick's avatar
    koryknick
    Employee
    2 years ago

    adam_gataev - since you have it in an array, you can use the Array.reduce() method to get the min or max value out of the array.  It would look like this:

    $dateArray.reduce ((accum,cur)=> accum == null || accum > cur ? cur : accum, null)

    Note that this also uses the ternary operator for the if/then/else condition to determine which value is greater when comparing  the array elements.  One nice feature of this method is that it doesn't matter what the data type of the values are, as long as they are comparable.

    There are other ways you could do this, but this was my initial thought.  If you're interested, I've attached an expression library that I created a while back and share with many of my clients as it has many common functions that have been requested.  To use the expr lib, just include it into the pipeline properties, then your expressions to get the min/max values just use the string.min() and string.max() methods.  Note the use of the spread operator to expand the array to individual values since the expr lib method is expecting a list of values rather than an array.

    Hope this helps!

    • adam_gataev's avatar
      adam_gataev
      New Contributor II
      2 years ago

      Hi koryknick,

      It worked, thank you so much! I am not sure if it will be a problem, but I just put the expression in the mapper snap instead of using an expression library file.

      Is there maybe a reason other than the readability benefit why you would use an expression library file?

  • koryknick's avatar
    koryknick
    Employee
    2 years ago

    adam_gataev - sorry for the delayed response - I've been OOO.  

    Having it in the Mapper expression works just as well.  The main reason why I like expression libraries is to make these types of calls re-usable and readily available for other developers who might be looking for the same functionality.