Hi @soulton
Your workflow's cleansing section should be stronger specially regarding DataTime fields, if you look at DateTime tool messages, it is telling you why those errors are happening:
There are a bunch of records having the Transaction Date with a format dd.M.yyyy, there are several ways to go about handling this situation, like shown below:
Here the data stream was split in two (because we are trying to figure out the reason for the error), we added a filter to the second data stream selecting records not having dots in their Transaction Date field, and the DateTime tools reported to error message, also, this filter (its F output) tolds as that the ConverPosted field is also formatted with dots instead of slashes.
Now that we know what's going on, we have different ways to handle the issue, one if replacing dots with slashes, or adding datetime tools to handle the different date format; you should notice that the date format for those dates with dots is dd.M.yyyy so you may need two datetime tools or a fancy formula to align your date formats.
Probably the simpliest approach is adding two DateTime tools, as so:
- I kept the analysis logic in the workflow, you should remove them once you understand why it was happening, producing a clean workflow.
- This solution is not 100% robust, as it handles M/dd/yyyy and dd.M.yyyy date formats with Transaction Date and Convert Posted sharing the same date format in the same record, it does not include other date formats like yyyy/M/dd or yyyy.M.dd; your workflow cleansing section should be able to handle all posibilities depending on your data, keep that in mind 🙂
Hope this helps,
Arnaldo
