topic Re: Store data into QVD saves timestamp as NULL in Connectivity & Data Prep

Store data into QVD saves timestamp as NULL

nicolaslopedebarrios — Mon, 14 Oct 2024 15:04:52 GMT

When extracting data from a MySQL table to a QVD file, most timestamp values are saved as NULL.

It is a simple script:

LIB CONNECT TO 'MySQL_MYDB (qs.user)'; LET vTabla = 'MY_TABLE'; $(vTabla): SELECT * FROM my_schema.my_table; STORE $(vTabla) INTO [lib://DAT_MARIADB_MYDB_QVD (qliksense_user)/DAT_01_MARIADB_MYDB_$(vTabla).qvd] (QVD); DROP TABLE $(vTabla);

When querying the database directly, there are no NULL or invalid values in the timestamp column. However, QVD metadata shows 95% NULLS (using QViewer):

As a result, every date related KPI is not reliable, since data is not consistent with the source.

What can I do to prevent this?

Re: Store data into QVD saves timestamp as NULL

Vegar — Tue, 15 Oct 2024 05:00:29 GMT

I have no straight answer, but check if your values are interpreted as null in the select or if the issue is in the conversion between resident table and qvd.

Try

LIB CONNECT TO 'MySQL_MYDB (qs.user)';

LET vTabla = 'MY_TABLE';

$(vTabla):

SELECT *

FROM my_schema.my_table

createe_at IS NOT NULL

LIMIT 100;

Re: Store data into QVD saves timestamp as NULL

nicolaslopedebarrios — Tue, 15 Oct 2024 12:43:21 GMT

Hi, the query in a MySQL editor shows there are no nulls. I had a similar problem lately, when Qlik finds an invalid value (this MySQL allows dates as '0000-00-00', I hate it) it starts loading null values instead, but this is not the case (timestamps are valid). I'm gonna try casting to date in the SELECT, and explicitly replacing zero-dates with null and see what happens.

Re: Store data into QVD saves timestamp as NULL

nicolaslopedebarrios — Mon, 21 Oct 2024 12:44:17 GMT

As I suspected, Qlik "doesn't like" MySQLs' zero-dates, so using a case when... then to save '0000-00-00' as NULL for every date column in the SQL statement solved the problem.