Forum Discussion

darshthakkar's avatar
darshthakkar
Valued Contributor
4 years ago
Solved

Transformation rules inside mapper snap not exported to output file

Hi Team, I have found one of the transformational rules applied on “date field” not getting exported to the final output file (i.e. excel file) when the pipeline has been executed. Output preview...
expression_language
pipeline
sample_pipeline
transform
  • ptaylor's avatar
    ptaylor
    4 years ago

    The Snowflake snaps have a setting called Handle Timestamp and Date Time Data. Unfortunately, its default setting, Default Date Time format in UTC Time Zone, has less-than-ideal behavior. The object type of the timestamp objects is java.sql.Timestamp, which will result in the odd inconsistencies you’re seeing between validation vs execution, and it’s also not a very usable type on the SnapLogic expression language.

    I suggest changing this setting to the other option, SnapLogic Date Time format in Regional Time Zone. This will convert the timestamps to the type org.joda.time.DateTime, the type that the SnapLogic EL functions are designed to deal with.

    This option will also produce a DateTime with the default time zone of the plex node, which can complicate things if the node’s default timezone isn’t UTC. But based on what you’ve shown, your node’s default timezone is UTC, so this shouldn’t be a problem for you, fortunately.

    After changing that setting, try using an expression like $Expire_Date.toLocaleDateTimeString({format: "yyyy-MM-dd'T'HH:mm:ss'Z'"}).

    Hope that helps.

darshthakkar's avatar
darshthakkar
Valued Contributor
4 years ago

This worked! Thank you, Robin.

However, the exported file has no values under dates, screenshot below for your convenience:

Just for my understanding, why would the Expire_Date not give us the same results? Is it because it’s an object vs Expire_Date.Timestamp a string? (Q-1)

Moreover, $Expire_Date.toString().replace('{Timestamp=',"").replace('.000+0000}',"Z") did give us the expected results during output preview of the snap and after validating the pipeline however the transformation didn’t work when the pipeline was executed, what would be the rationale behind it? (Q-2)

I want to understand this in details so that I can learn snapLogic in a better fashion and be a good snapLogic developer. Please don’t get me wrong.

Best Regards,
Darsh

darshthakkar's avatar
darshthakkar
Valued Contributor
4 years ago

In addition to this,

Output preview is different on the field and mapper snap, screenshots below:

Isn’t this strange as the same snap which shows a preview as expected output in one of the fields shows a null when doing a sanity check on the entire snap.

  • Supratim's avatar
    Supratim
    Contributor III
    4 years ago

    @darshthakkar You can check the setup of excel formatter. But I would suggest go with CSV formatter rather excel formatter , and create file with .csv extension.

    !

    • darshthakkar's avatar
      darshthakkar
      Valued Contributor
      4 years ago

      Thank you, @Supratim for the suggestion.
      If you check my previous responses, I’ve tried both with excel and csv formatter and it doesn’t work. I’ve also shared the screenshots of excel formatter in my first comment which can be accessed here

      Moreover, I want an excel file and not csv.