How to set a transformation on timestamp column that is zero in value

[desmondchew]

One of our task has failed due to a zero value in its datatype "AcceptedDateTime" column. Can you please let me know how to get the transformation on this? We currently have this setup but it doesn't seem to work.

case substr(cast($AR_M_SOURCE_COLUMN_DATA as text), 1,4) when '0000'

then

datetime('1900-01-01')

else

$AR_M_SOURCE_COLUMN_DATA

end

Table 'DBx'.'EX92' (subtask 16 thread 1) is suspended. Failed (retcode -1) to execute statement: COPY INTO "SCHEMA"."TABLE92"("C92", "S92", "S92", "SS", "DateStamp", "TYPE", "AcceptedDateTime", "S2", "TEXT") FROM (select $1, $2, $3, $4, $5, $6, $7, $8, $9 FROM '@"PROD"."SCHEMA"."ATTREP_IS_PROD_e402d77b_6838_b648_825e_2cb5b5669bf6"/2/16/') files = ('LOAD00000001.csv.gz') force=true; RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00.000000000' is not recognized
File '2/16/LOAD00000001.csv.gz', line 1, character 76
Row 1, column "TABLE92"["AcceptedDateTime":7]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.; Failed (retcode -1) to execute statement:

[DesmondWOO]

Hi @desmondchew ,

Thank you for reaching out to the Qlik Community.

I've tested your expression. It should be fine. Could you send us following information?

• Source endpoint and Target endpoint
• Datatype of the source column and target column

Regards,
Desmond

[desmondchew]

Hi Desmond,

We are replicating from MySQL 8 on premise into Snowflake AWS.The datatype on both endpoints are datetime column.

The Qlik Replicate is throwing out error even with the transformation.

What is this AR_M_SOURCE_COLUMN_DATA in the transformation?

case substr(cast($AR_M_SOURCE_COLUMN_DATA as text), 1,4) when '0000'

Thank you.

Desmond

[Heinvandenheuvel]

Desmond Chew>> What is this AR_M_SOURCE_COLUMN_DATA in the transformation?

Generic data value when using GLOBAL transformation, where the actual column name is not know.

Is this in the context of a global transformation? Does it have to be? Can you keep it simple and use a per-(date) column transformation only if/when needed?

>>> cast($AR_M_SOURCE_COLUMN_DATA as text)

The SQLITE CAST function is not documented for Replicate and as such not supported. It appears to work, but I don't think you actually need it, so just leave it out?

>>> substr(cast($AR_M_SOURCE_COLUMN_DATA as text), 1,4) when '0000'

That only catches a specify  bad case. I recommend making a more generic test.

case when 0 + substr($AR_M_SOURCE_COLUMN_DATA, 1,4) > 1900
then $AR_M_SOURCE_COLUMN_DATA
else '1900-01-01'
end

Note 1) the usage of 0 + in order to cast to a numeric comparison.

Note 2) You don't need the datetime() function around a date string.

Note 3) Check out strftime ('%Y',$date-value). This will pick up just the year for easy testing, but it will validate the full date value such that for example 2000-16-01 will also fail. 

Hein.

[desmondchew]

We know the column "AcceptedDate" in EX92 is causing the issue. Which transformation syntax should we use as a workaround please?

This table is huge and taking a long time to reload and then fails. Please help.

Thank you.
Desmond

'@"PROD"."SCHEMA"."ATTREP_IS_PROD_e402d77b_6838_b648_825e_2cb5b5669bf6"/2/7/') files = ('LOAD00000001.csv.gz') force=true; RetCode: SQL_ERROR SqlState: 22007 NativeError: 100035 Message: Timestamp '0000-00-00 00:00:00.000000000' is not recognized
File '2/7/LOAD00000001.csv.gz', line 1, character 76
Row 1, column "EX92"["AcceptedDate":7]
If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option.

[john_wang]

Hello @desmondchew ,

I think @Heinvandenheuvel comment explains the usage in details. Does the expression work for you? or you are facing another challenge? Feel free to let me know your accurate expression and the results.

thanks,

John.

[sureshkumar]

Hello @desmondchew 

Kindly Check below community article and see if it helps

Transformation: Date Time - Invalid Value - MySQL - Qlik Community - 1826202

Regards,

Suresh

[Heinvandenheuvel · Accepted solution]

@sureshkumar - Transformation: Date Time - Invalid Value - MySQL - Qlik Community - 1826202

Ah, that article uses AR_M_SOURCE_COLUMN_DATA 

@desmondchew  if you read on, you will notice a section - "NON Global Transformation" - that's what applies here. SO your COLUMN transformation could/should look like. 

case when 0 + substr($AcceptedDare, 1,4) > 1900
then $AcceptedDare
else '1900-01-01'
end

Note 1- The '0000' is probably NOT on the source, but a consequence of a failed conversion due to a bad date.

Note 2- Don't just run of and prepare for prod. Use a DEV box and a SMALL sized TEST table with a KNOWN bad date or two in a  Be sure to see the TEST table fail, then apply the transform and see if that fixes it to your liking.

Hein.