Forum Discussion

UrsVinny's avatar
UrsVinny
New Contributor II
3 years ago

Ternary operation to handle diff date formats

The source file contains Reporting Period value in diff date format.
Aug-2022
Aug-22
AUG 2022
August-2022
I am using the below ternary expression to handle the above mentioned date pattern.
isNaN(Date.parse($RPT_MONTH,‘MMM-yyyy’)) || isNaN(Date.parse($RPT_MONTH,‘MMM-yy’)) ? $RPT_MONTH : Date.parse($RPT_MONTH,‘MMM-yy’).toLocaleDateString({“format”:“MMM yyyy”})

But when the value is in August-2022 the above code is not converting it into AUG 2022

Any kind of help will be highly appreciated.

Cheers
Vinny

9 Replies

  • del's avatar
    del
    Contributor III
    3 years ago

    I’m just playing with regex ideas for this (because who doesn’t like to - regex is like play doh! 😄), I think this will work for a mapper if you know it is a single delimiter being a space or a dash:

    Date.parse($RPT_MONTH.replace(/^([^ -]+).*(\d{2})(\d{2})$/,"$1-$2$3"),'MMM-yy').toLocaleDateString({"format":"MMM yyyy"})

    But you could cover your bases in case the delimiter is not a space or dash or no delimiter at all (ex. Aug.2022 or Aug22) with either one of these two:

    Date.parse($RPT_MONTH.replace(/^([^\d]{3}).*(\d{2})(\d{2})$/,"$1-$2$3"),'MMM-yy').toLocaleDateString({"format":"MMM yyyy"})
Date.parse($RPT_MONTH.replace(/^([a-zA-Z]+).*(\d{2})(\d{2})$/,"$1-$2$3"),'MMM-yy').toLocaleDateString({"format":"MMM yyyy"})

    Disclaimer: testing works for me with the values of the original post and a few I threw in of my own. All potential cases are not fully tested.

    • del's avatar
      del
      Contributor III
      3 years ago

      Follow-up to my post: In my testing, I found that Date.parse() format of “MMM-yy” works correctly with both 2-digit and 4-digit years, so it may not be necessary to differentiate in your logic.

  • del's avatar
    del
    Contributor III
    3 years ago

    First thing I did this morning was reviewed my proposed solution. This solution is feasible for narrow windows of time, which would be the assumption required to use it. However, there is a caveat on the century concern, which Kory brought up first, which is that if we’re reading a large window of time (for instance, say we’re trending inflation since WWII {I know, an extreme case, but makes the point}), then looking at “AUG-42” today using Date.parse() would result in “1942”, but if we process the exact same dataset this time next year, it will result as “2042” due to Date.parse() behavior around 19 vs. 20 years out. Very unreliable in that way.

    So, use with caution.

    Soapbox warning… I can’t figure out how we rounded Y2K and still have datasets that have 2-digit year representations. In my opinion, this should be fixed at the data source, where it is broken, and not rely upon fuzzy logic to try and figure it out by best guess. While solutioning this, I would have so many questions and caveats with which to annoy the requesting stakeholders.

  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor
    3 years ago

    Hi @UrsVinny ,

    You can always extend the ternary operator with another nested ternary, but that could very easily become a complex and unsustainable expression.

    I would recommend an expression library with a function that will check the date field for all possible formats.

    That way you can easily add a new format in the future if needed.

    Regards

  • koryknick's avatar
    koryknick
    Employee
    3 years ago

    @UrsVinny - Sorry I originally posted a little too quickly and didn’t validate my reply.

    Here is a small sample pipeline using the Conditional snap to test if it’s a 2-or-4 digit year before doing the Date.parse()
    Community 13660 - Date parsing_2022_10_19.slp (5.4 KB)

    It’s pretty easy to add expressions in the Conditional. As a general rule, I typically revert to the Conditional snap when the ternary logic requires more than one test since ternary gets hard to decipher after just a couple levels.

    Hope this helps!

  • koryknick's avatar
    koryknick
    Employee
    3 years ago

    @del - did you check to ensure it was the right century? The parse works but I think the year is wrong.

    • del's avatar
      del
      Contributor III
      3 years ago

      @koryknick,

      The century is a good call-out and something to consider for the case. I did consider the century and I’m leaving the century of the two-digit year to be determined by the Date.parse() interpreter as I don’t know the actual use case, the source data, or full requirements of whether it is intended to be historical or future/predictive reporting.

      In any case, the “$2” replace output (of the above) could be replaced by a hard-coded “20” value or other round-of-century logic calculation if the date is always future dated and Date.parse() picks incorrectly for certain scenarios. My testing shows Date.parse() chooses the current century for around 19 years added (41 converts to 2041) and then backdates a century about 20 years added (42 converts to 1942).

      Meanwhile, I shortened the regex further with current 2-digit logic - producing the same results as the others . (Like I said - regex is Play Doh)

      Date.parse($RPT_MONTH.replace(/^(.{3})[^\d]*(\d{2,4})$/,"$1-$2"),'MMM-yy') .toLocaleDateString({"format":"MMM yyyy"})

      (☝️ 40-minute later edit to code snippet to remove inserted hard-coded value)

      Here’s some testing output:

  • koryknick's avatar
    koryknick
    Employee
    3 years ago

    Great solution. Love the regex usage. Thanks for the response to my query!

  • UrsVinny's avatar
    UrsVinny
    New Contributor II
    3 years ago

    @koryknick
    Sorry for responding so late…I was able to fix the issue using ternary operator but nevertheless usage of regex is more kool than Ternary operator.

    Thanks once again to each and every one.