Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Maybe use something like
Date( Alt(DATEFIELD, Date#(DATEFIELD,'DD-MM-YYYY') )) as DATEFIELD
when loading your data
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
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.
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.
Maybe use something like
Date( Alt(DATEFIELD, Date#(DATEFIELD,'DD-MM-YYYY') )) as DATEFIELD
when loading your data
No!!!! I can't believe it was that simple . I've been struggling for days...
Thank you so much, Stefan!