Transformation rules inside mapper snap not exported to output file

[darshthakkar]

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 on the snap does show the right data (i.e. expected one), pipeline validation also shows the right data however the file which gets generated after pipeline execution doesn’t get those details.

I have tried the following:

• validated pipeline via shift+validate
• tweak the transformation rule, save the pipeline and check the output in the preview
• validated the preview file (with default 50 records)
• deleted the file generated by “validate & execute” snap on File writer with default 50 records so that new instance of data comes in
• executed the pipeline with different file name multiple times

It’s a simple pipeline consisting of

            Snowflake Execute --> Mapper --> Excel Formatter --> File Writer

Sharing some screenshots to help it understand better:
(1) Pipeline with 4 snaps

(2) Snowflake execute output preview in JSON format (highlighted yellow fields needs transformation)

(3) Transformation Rules on Date fields

(4) Transformation Rule: $Expire_Date.toString().replace('{Timestamp=',"").replace('.000+0000}',"Z")

(5) Output preview after transformational rule

(6) Mapper output preview shows different data

→ I’ve observed certain times that the transformation rules won’t work so I go inside the mapper, modify the rule a bit, bring it back to default rule so that I can save it (save is not enabled if there is no change thus this workaround)

(7) As shift+validate didn’t work, had to change the transformation rule, save it and then this is the output preview on mapper snap:

(8) Settings under excel formatter snap for reference:

(9) Settings under File Writer snap for reference:

(10) File generated with default 50 records due to validate & execute on File writer snap

(11) Downloaded the file that gets generated in step 10 and below is the output (expected)

(12) Executed Pipeline

(13) Output File of Executed pipeline

---

---

I’ve seen this happening numerous times particularly to fields containing timestamp in other pipelines too, am I doing something wrong over here? Any settings that needs to be changed either in Mapper, Excel Formatter or File Writer snap?

Thanking in advance for your valuable time and suggestions on this one.

Best Regards,
DT

[darshthakkar]

robin:

Date.parse($Expire_Date.Timestamp).toLocaleDateTimeString({format: "yyyy-MM-dd'T'HH:mm:ss'Z'"})

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]

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]

@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]

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.

[darshthakkar]

Providing a summary of the transformational rules applied so far:

1. $Expire_Date.toString().replace('{Timestamp=',"").replace('.000+0000}',"Z") → Output Preview [works expected] → Exported Excel File [transformation rules don’t appear to work]

2. $Expire_Date.toLocaleDateTimeString({format: "yyyy-MM-dd'T'HH:mm:ss'Z'"}) → Output Preview [shows Null values] → Exported Excel File [all blank values]

3. Date.parse($Expire_Date.Timestamp).toLocaleDateTimeString({format: "yyyy-MM-dd'T'HH:mm:ss'Z'"}) → Output preview [works expected] → Exported Excel File [all blank values]