topic Re: Qlik Replicate - load fails with data error in Qlik Replicate

Qlik Replicate - load fails with data error

KrzysztofNa — Tue, 31 May 2022 09:22:11 GMT

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.

Re: Qlik Replicate - load fails with data error

Steve_Nguyen — Tue, 31 May 2022 12:01:44 GMT

@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) ?

Re: Qlik Replicate - load fails with data error

KrzysztofNa — Tue, 31 May 2022 13:33:00 GMT

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

Re: Qlik Replicate - load fails with data error

Steve_Nguyen — Tue, 31 May 2022 15:43:52 GMT

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-p/1891227

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

Re: Qlik Replicate - load fails with data error

lyka — Fri, 15 Jul 2022 18:12:07 GMT

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/Customize%20Tasks/tasks_expressOther.htm

Hope this helps!

Thanks

Lyka

Re: Qlik Replicate - load fails with data error

Michael_Litz — Fri, 15 Jul 2022 18:18:08 GMT

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