Bad Date

[fanninam]

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)

[Vegar · Accepted solution]

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

[fanninam]

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';

[Vegar · Accepted solution]

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

[fanninam]

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,