Date column not recognised as Date due to presence of the year 1800 & 3000

[RiskFerdinand]

Hi everyone, 

My table contains a column with dates mostly in the range of 1990-2020, however a portion contains the date '01-01-1800' or '31-12-3000' (due to various reasons, these dates should not be changed). However, Qlik therefore doesn't recognise my date column as Date, and prevents me from applying it on e.g. the Date Picker.

The column does get recognised as Date by Qlik when the rows containing dates 01-01-1800 & 31-12-3000 are removed from the table, however I don't want to lose these observations. 

Therefore my question is: How can I let Qlik recognise 01-01-1800 & 31-12-3000 as dates, such that the whole column while be eligible as Date? 

I read about the Tag function, however I'm not sure how to specifically apply this.

Thank you in advance!

Ferdinand

[Chanty4u]

is it not treating as date? or how?

Date(Date#( YourField, 'DD-MMM-YY')'DD-MMM-YY') as NewDateField

[RiskFerdinand]

I applied your formula in the data load editor on my field with dates, but Qlik still doesn't treat the column as Date (see attachments)

[Kushal_Chawda]

@RiskFerdinand  can you send screenshot of data in that New date field?

[Kushal_Chawda · Accepted solution]

@RiskFerdinand  as your Date contains some odd values Date picker won't directly recognize so you need to play some trick

First try loading the Data with where condition (load data between year 2000 to 2050)

Data:
Load * where year(Date)>2000 and year(Date) <= 2050;
LOAD * ,
     Date
FROM Table;

Now, make sure that your Date field is in proper Date format and add that Date field in Date Picker. 

Once you add Date field in Date Picker, remove where condition added in script and load full Data. Then, you Date picker will identify have all Dates

[RiskFerdinand]

@Kushal_Chawda Awesome, your tricks works. Thanks!