Qlik Replicate - load fails with data error

[KrzysztofNa]

I have performed full load of the large set of data using Replicate. After about a day of running extraction failed due to a record with a typo in data format - YYYYMMDD written accidentally in reversed format, which caused an error during data parsing - was read by qlik as DDMM - YY -YY  and this stopped an extraction. Because of the amount of data, I would like to avoid process failures for similar cases in the future, just because of time. Is is possible to set some option to not interrupt the extraction for similar cases, but just generate some warnings to logs? It seems, that Error Handling settings change in Task Settings did not help. 

[Steve_Nguyen]

@KrzysztofNa

could you provide the replicate log we want to see where the error happen , was this on the source or target error ?

was the error on target_load (full load) or on target_apply (CDC) ?

[KrzysztofNa]

@Steve_Nguyen 

This was the source error - wrong data in one column, which was finally resolved. Error was related to the full load, here is a part of log:

00021971: 2022-05-28T07:34:10 [TARGET_LOAD ]E: Command failed to load data with exit error code 1, Command output: ERROR: date/time field value out of range: "1509-20-21"
HINT: Perhaps you need a different "datestyle" setting.
CONTEXT: COPY ACDOC, line 1069, column AUGDT: "1509-20-21" [1020417] (csv_target.c:1008)

The thing is, that I would like avoid long extraction failures with similar ones for the future, but just receive warnings into logs. Is there any setting in replicate to do that? 

[Steve_Nguyen]

since is a full load, not much in term of recovery.

what you can try and test is using passthrus filter for date:

https://community.qlik.com/t5/Qlik-Replicate/Filtering-duplicates-using-Fullload-Passthru-Filter/td-...

https://community.qlik.com/t5/Knowledge/Filter-for-last-90-days-of-data-in-Qlik-Replicate/ta-p/18807...

[lyka · Accepted solution]

Hello,

You can try to use a transformation:

strftime(format, timestring, modifier, modifier...)

More information here:

https://help.qlik.com/en-US/replicate/May2022/Content/Global_Common/Content/SharedEMReplicate/Custom...

Hope this helps!

Thanks

Lyka

[Michael_Litz · Accepted solution]

Hi,

Here is an example of using date functions in a field level transformation:

substr(strftime("%Y-%m-%d", $MLDateTime),3)

In this case I am using strftime to format the date  and the substr is being used to have a 2 digit year.

Since the transformations are based on SQLite you can find good documentation on SQLite syntax on line.

Thnaks,
Michael