Hello everyone,
I am trying to import a Timestamp field "DATUM" from a Firebird databse. The field type in the database is "Timestamp" and when I select the field with SQL via an external tool I can see that the dates have been recorded in two formats DD.MM.YYYY hh:mm:ss AND DD.MM.YYYY which might be the cause of the problem.
When I import the field with the Data manager, Qlik Sense sets the field to "General" and does not recognize this field as Timestamp type. I then change the field type to "Timestamp", input format DD.MM.YYYY[ hh:mm:ss], display format to DD.MM.YYYY. So far all ok, and I can see the dates in the column correctly dispayed.
I then go to App View, Open a Sheet and I can see the field "DATUM" correctly recognized by Qlik Sense as Date field with the automatic calendar options (Year, Month, etc..). Now, when I add a visualization, e.g. a Table, and add the "DATUM" field, no data is displayed.
I then go back to the Data manager and edit the table, and now the "DATUM" field is greyed out and no values are displayed.
Can anyone tell me why this is happening?
I managed to make it work correctly by unklocking the auto-generated load script and changing the line in the LOAD section from
Timestamp(Timestamp#([DATUM], 'DD.MM.YYYY[ hh:mm:ss]') ,'DD.MM.YYYY') AS [DATUM],
TO
Timestamp([DATUM]) AS [DATUM],
and everything worked perfectly.
The problem is that I do NOT wish to unlock the auto-generated script as I am still new to Qlik and I wish to continue using the Data manager tool to import data.
Something else I have tried was to leave the DATUM field imported as "General" and create a calculate field with the Expression "Timestamp(DATUM, 'DD.MM.YYYY')" (dates displayed are aligned to the right hand side) and even "Timestamp#(DATUM, 'DD.MM.YYYY')" (dates displayed are aligned to the left) and a combination of both, as well as with the fuctions "Floor". These have not helped since Qlik will not recognize the calculated field as dates and will not provide the automated calendar options. In the load script I can see that the calculated field is not displayed in the "Derived fields from fields" under autoCalendar.
Any help is very much appreciated.
Best regards
