Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
fanninam
Creator
Creator

Bad Date

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)

2 Solutions

Accepted Solutions
Vegar
MVP
MVP

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

View solution in original post

Vegar
MVP
MVP

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

 

 

 

 

View solution in original post

4 Replies
Vegar
MVP
MVP

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

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
MVP
MVP

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
Creator
Creator
Author

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,