topic Re: Bad Date in QlikView

Bad Date

fanninam — Wed, 25 Nov 2020 16:16:04 GMT

I need to create a QVD that will do the following because I'm dealing with a bad date in my data. I'm having a hard time finding the correct syntax for this in Qlikview. Does anyone have any suggestions for me?

decode(to_char(TSSOC240.T$INVD,'dd/mm/yyyy'),'01/01/4712',to_date(null),TSSOC240.T$INVD)

Re: Bad Date

Vegar — Tue, 19 Mar 2019 19:35:42 GMT

Could upp some samples off correct and bad date entries and try to describe how your want QlikView to handle these bad values?

Re: Bad Date

fanninam — Tue, 19 Mar 2019 20:13:51 GMT

My current script is as follows. I need the bad dates within the T$INVD field to be recognized by Qlikview as a date that is null. This way when I filter where T$INVD >= '01/01/2019' Qlikview doesn't remove the record all together.

TTSSOC240750:
LOAD
T$ORNO,
T$INVD,
Year(T$INVD) AS Year,
Month(T$INVD) AS Month,
T$PRIS

FROM
\\ftwqpubt001\Qlikview_Dev\QVD\ORA_BAAN\TTSSOC240750.qvd
(qvd) where T$INVD >= '01/01/2019';

Re: Bad Date

Vegar — Tue, 19 Mar 2019 22:07:20 GMT

What does the date output of T$INVD look like? It is possible to help QlikView understand the format by using date#()

If T$INVD looks like this format '31/12/2018' then you could write

date#(T$INVD, 'DD/MM/YYYY') as T$INVD

IF the format is like 2018-12-31 you could write

date#(T$INVD, 'YYYY-MM-DD') as T$INVD

This will only intrepret the T$INVD as a date, not present it in the format you desire, to make it look like your default date format you could wrap date() around the expression like this.

date(date#(T$INVD, 'DD/MM/YYYY')) as T$INVD
OR 
date(date#(T$INVD, 'YYYY-MM-DD')) as T$INVD

Re: Bad Date

fanninam — Wed, 20 Mar 2019 12:25:10 GMT

The date output looks like this:

IF(date(date#(T$INVD,'12/29/4294962183 12:00:00 AM'),Date#(Null())),T$INVD) AS T$INVD,