Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
RiskFerdinand
Contributor II
Contributor II

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

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

Labels (6)
1 Solution

Accepted Solutions
Kushal_Chawda

@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

View solution in original post

5 Replies
Chanty4u
MVP
MVP

is it not treating as date? or how?

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

 

RiskFerdinand
Contributor II
Contributor II
Author

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

@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
Contributor II
Contributor II
Author

@Kushal_Chawda Awesome, your tricks works. Thanks!