Pre 1900 Dates

[jacqueshol]

Hi All,

I completed a similar request a few minutes ago, but it seemingly hasn't posted.

I need some assistance with dates preceding 1900.  I have a flat file (Excel) with about 500 rows and 26 columns of data that represent sporting results going back to 30-07-1891 (30 July 1891).

I initially built the model in QlikView 11 and had absolutely no problems with dates. I could derive month, year, day selections from all the dates, including those prior to the epoch date (30-12-1899).  We then decided to rather build the model in Qlik Sense and while everything is working fine, dates have become a problem.  I can't create month and year values on dates pre-1900.

Can anyone direct me to an article dealing with this issue or advise as to how to deal with these dates as I obviously cannot exclude this data from my dataset?  I have searched the internet extensively for assistance on this, but haven't been able to find a solution.

Thank you kindly,

Jacques

[RonaldDoes]

Hi Jacques,

First of all, I'm not certain your question is best asked in the QlikView section of the community since the problem appears to be strictly related to Qlik Sense.

However, I can not reproduce the behaviour you're describing. I've loaded some dates pre- and post-epoch and Qlik Sense appears to be handling this rather elegantly.

Can you share (a portion of) your app and data model along with the expected output to further investigate what's going on?

With kind regards,

Ronald

[jacqueshol]

Hi Ronald,

Thank you kindly for the quick response.  Apologies for asking the question in the QlikView section.  I actually thought it was in the Qlik Sense section

Anyway, a small extract of my flat file looks as follows:

No	Date	Opponents	Venue	Points For	Points Against
1	30-07-1891	Britain	Port Elizabeth	0	4
2	29-08-1896	Britain	Kimberley	3	9
3	17-11-1906	Scotland	Glasgow	0	6
4	16-11-1968	France	Paris	16	11
5	06-11-2004	Wales	Cardiff	38	36
6	30-09-2017	Australia	Bloemfontein	27	27

When I import the data, the dates in lines 1 and 2 (pre-1900's) import as text.  E.g. I cannot use functions such as year(Date) as year on these.  These values aren't seen as numbers. I don't have this problem in QlikView, only Qlik Sense. I've attached a screenshot that highlights how Qlik Sense differentiates between pre-1900 (highlighted in yellow) and those after 1900 (encircled in red).

Therefore, I can only derive years, months, etc for all the dates after 1900, but not those before. Rather frustrating to say the least.

[jacqueshol]

Ok, I think I discovered the issue.  The data has been captured in Excel and therefore the pre-1900 dates are not seen as a numeric value, but as text in Excel.  That is probably why Qlik Sense imports it as text. QlikView, however, was smart enough to circumvent this problem. A shame Qlik Sense could not.

I am still not too sure how to fix this problem.  Perhaps I need to save all the dates in Excel as text and import it into Qlik Sense. Then I perhaps have to see if Qlik Sense has the "sense" (no pun intended ) to convert it all to numeric dates, either automatically or manually.

[swuehl · Accepted solution]

Maybe use something like

Date( Alt(DATEFIELD, Date#(DATEFIELD,'DD-MM-YYYY') )) as DATEFIELD

when loading your data

[jacqueshol]

No!!!! I can't believe it was that simple . I've been struggling for days...

Thank you so much, Stefan!