Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Qlik Community,
I want to convert data '1753-01-01 00:00:00.000' from a database in a NULL value in QlikView report.
I use the following -
SET NullTimeStamp = if ($1 = '1753-01-01 00:00:00', null(), $1);
Then use it when LOAD:
LOAD
$(NullTimeStamp(YourDateField1)) AS YOURDATEFIELD1,
$(NullTimeStamp(YourDateField2)) AS YOURDATEFIELD2,
$(NullTimeStamp(YourDateField3)) AS YOURDATEFIELD3
But I since I have many time and date records I was wondering if there is some more elegant way of solving this issue?
Thanks,
Ana
maybe like this?
SET NullInterpret='1753-01-01 00:00:00.000';
table1:
LOAD RecNo() as ID, *
Inline [
YourDateField1, YourDateField2, YourDateField3
2022-08-03 22:37:30.000, 2022-08-03 22:38:30.000, 2022-08-03 22:39:30.000
1753-01-01 00:00:00.000, 2022-08-03 22:40:30.000, 2022-08-03 22:41:30.000
2022-08-03 22:42:30.000, 1753-01-01 00:00:00.000, 2022-08-03 22:43:30.000
2022-08-03 22:44:30.000, 2022-08-03 22:45:30.000, 1753-01-01 00:00:00.000
1753-01-01 00:00:00.000, 1753-01-01 00:00:00.000, 1753-01-01 00:00:00.000
1753-01-01 00:00:00.000, 1753-01-01 00:00:00.000, 2022-08-03 22:46:30.000
1753-01-01 00:00:00.000, 2022-08-03 22:47:30.000, 1753-01-01 00:00:00.000
2022-08-03 22:48:30.000, 1753-01-01 00:00:00.000, 1753-01-01 00:00:00.000
];
Yes, thank you for the different solution. It really helped me.