Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have been working with QlikSense for a few years and we have come across a problem we have not seen before.
We have some Excel CVS date-dump files we need to load into our environment (QlikDesktop & QlikServer) and within that file is a date column.
The Raw (unedited) CVS files has the date in the following format:
DD-MMM-YY
or
30-Sep-26
If we load the files either by the drag and drop mechanism or auto create a script the dates are expanded to the following:
30 September 2016
And seem to be loaded as "Text Strings" vs Dates. So clearly, Qlik understand that the field is a date and expands the month name.
In other columns we have seen the following conversion to fix (among other suggestions)
Date(Date#( YourField, 'DD-MMM-YY'),'DD-MMM-YY') as NewDateField
Which as I understand it, will carry two versions of the date - one a string and the other a numeric date... We have not been able to get this to work in any way or combination of parameters or following any of the recommendations found among others notes.
Our end goal is to use the date in at least two ways: 1) sorting charts by date and 2) determining the difference between two dates; so it is critical to be able to use the date as a numeric date.
Your help with our understanding would be greatly appreciated.
Frank
So, without transformation... you are seeing this -> 30 September 2016 in qlik? If this is what you are seeing, then try this
Date(YourField, 'DD-MMM-YY') as NewDateField
or may be this
Date(Date#(YourField, 'DD MMMM YYYY'), 'DD-MMM-YY') as NewDateField