Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Date recognition problem
Hi All,
I have a date recognition issue on QlikSense when I connect my app to the GDMS + database. I don't have the small calendar logo on my original_creation_date field unlike my other dates fields which means that the field is not recognized as a date. So this poses problems for me when it comes to making graphics. I saw with the data administrator of the GDMS + database that there is no problem concerning the dates.
I have already consulted almost all the posts concerning dates on the internet and in the Qlik community to which I posted a publication 6 months ago to which no one was able to respond. None of the posts clearly answer my problem or they are too complicated solutions that are beyond my Qlik skills and are not too detailed to be able to carry out the maneuver. All that is to write scripts in the Load Script will prevent me from using my dashboard properly since once the load script has been touched we can no longer create indicators or graphs only with clicks (which is a bit difficult). goal of QlikSense) but we will have to do them via script so I would like to avoid this solution unless it is the only one.
What I want is to have my date fields in AutoCalendar on QlikSense. As we can see in the screenshot below, some of my fields are indeed in autoCalendar which allows me to select the day, month, year, semester, etc. of that date.
However, on some fields there is no this logo which means that it is in autoCalendar. I observed the data in the Data Manager tables and noticed that their format was not dated.
As we can see in the screenshot below, the r_creation_date field is indeed in autoCalendar because it is recognized as a Timestamp variable unlike the right variable which has no format (it can be recognized in text as you said in your email but that would surprise me since it is many dates that we see there). The problem is that it is strange we find dates "1/1/0001" and all at "12:00:00 AM" which does not exist in the GDMS + database. Which leads me to believe that Qlik misinterpreted the data.
However, when I click to change the interpretation format and put it in Timestamp, I am asked for an input format and a display format, but I don't know what the input format is? How can I find out?
So I tried MM / DD / YYYY but it still does not work and it replaces my data with dashes ...
I found the input format for these dates: M / D / YYYY h: mm: ss [.fff] TT. By changing the field type in the data manager with the display format: M / D / YYYY.
I managed to see small arrows in my fields as if they were dates in AutoCalendar. As you can see in the screenshot it still lacks the calendar logo like the variables that are actually in autoCalendar so I thought that was not going to work.
Indeed, I tried to choose any of the fields eg "Year" of the PERIODIC_REVIEW_DATE variable and display it in a table.
This returns empty values to me. The other variables which are in autoCalendar with the calendar logo to their left display their values well.
What is the cause of this problem?
What is weird is that QlikSense finds me 137.95k of date in 1/1/0001 when there is none in the database.
Which comforts me in the idea that QlikSense misinterprets these dates, what should I do? How to explain that QlikSense correctly interprets 2 date variables and puts them in autoCalendar and 4 other date variables are misinterpreted even though they are in the same format?
Thank you in advance for your help !
Waïl Riachi
