Forum Discussion

darshthakkar's avatar
darshthakkar
Valued Contributor
4 years ago
Solved

Fetch data whose date is less than 10hrs from the existing date!

Hi Team,

I’m trying to achieve a filter condition but haven’t found any luck so far. My data has a field as Last_Updated which has date stored in the format 2022-06-23 03:54:45

I want to consider only those records whose date is less than 10 hours than the existing date. How can I achieve this? Shall I use a mapper or a filter snap and I would really appreciate if the logic behind this can be shared.

If the format of the date that is stored in the Last_Updated needs to be transformed as well, please let me know.
Thanks!

Regards,
Darsh

expression_language
pattern
sample_pipeline
transform
  • bojanvelevski's avatar
    bojanvelevski
    4 years ago

    Than I go back to my original statement. This expression will do the job:

    Date.parse($Last_Updated) > Date.now().minusHours(48)

16 Replies

  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor
    4 years ago

    You can do something like this:

    Date.parse($Last_Updated) > Date.now().minusHours(10)

      • bojanvelevski's avatar
        bojanvelevski
        Valued Contributor
        4 years ago

        Now when I reviewed your original post, yes, it should be “<”. You want to consider dates that are less than 10 hours than the current date.

  • koryknick's avatar
    koryknick
    Employee
    4 years ago

    @darshthakkar - I would recommend carefully considering if the Diff snap is really what you want to use. The Diff snap compares the incoming JSON documents as a whole for equivalence - if anything in the objects are different, it considers it as modified. This includes everything from a difference in the value of a given element, the order of the elements within the object, missing / added elements, element name case sensitivity, or even differences in datatypes.

    I think what you probably want to consider instead is the use of a Join snap - probably using Left Outer join type - to check the existence of the target record and apply insert/update methodology based on that condition.

    Also, rather than opening a new discussion on a closed topic, please open a new thread so the information shared is under a logical heading for other members to find more easily.

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      4 years ago

      I agree to your comments on opening a new thread. I will do the needful.

      Regarding Diff snap, I haven’t used it so far, or to put it correctly, I wasn’t even aware of that until today.
      So, I have used Joins, Inner join to be precise and then routing the error view to get the actual output.

      The concerning part is duplicates so how would I eliminate those will require some serious brainstorming from my side.

      Thank you for your suggestion on new thread.

      Regards,
      Darsh

  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    4 years ago

    @bojanvelevski: I do understand that we closed this thread as we got the desired solution however putting a comment here as the next thing I’m trying to achieve is related to this functionality itself.

    We were considering all records whose Last_Updated was less than 48 hrs and then those records were set to update the Salesforce table.
    After that, a scheduled job was enabled to run 4 times a day and as we are considering records updated within last 48 hrs, we are seeing duplicate records in each batch, I believe we will continue to see those as the criteria is less than 48 hours, is there a way we can filter out what was updated in the last batch and consider the brand new ones ONLY?

    Thanking you in advance for your help 🙂

    Regards,
    Darsh

    • bojanvelevski's avatar
      bojanvelevski
      Valued Contributor
      4 years ago

      In order to exclude duplicates, you’ll have to use a Diff mechanism. Practically, when get records that are updated in the last 48 hours, you need to compare them with Salesforce records added in the last 48 hours by using a Diff snap.

      • darshthakkar's avatar
        darshthakkar
        Valued Contributor
        4 years ago

        Thank you @bojanvelevski, cheked Diff snap, wasn’t aware of this up until now. It suggests that the input streams should be sorted, I will have to use a sorter as my entire pipeline was unsorted.

  • darshthakkar's avatar
    darshthakkar
    Valued Contributor
    4 years ago

    @bojanvelevski: From the documentation on Diff snap, it can be understood that the input documents should be flat files. For my use case, I will have to compare 2 different systems, one is snowflake and another is Salesforce plus after the comparison I need to update Salesforce.

    Does Diff snap allow us to update records to Salesforce if we put a salesforce upsert/salesfroce update after Modified path of Diff?

    Thanks!