Download as Data with Table Formatting causes Excel error
Hello.
This is not so much a question as an observation that developers might want to be aware of. I've already brought this scenario up with Qlik support and they say "Works as designed".
The specific scenario is thus:
When you load data into a Qlik app, if you don't explicitly configure the data types, Qlik may compare string variables to the SET DateFormat pattern and automatically convert these values into dates. That's okay, I actually think this is a nice feature. And if you display these date values in a Table, they will display in the DateFormat pattern. Again, this is okay.
The issue I have is that, even though Qlik is treating these date values as having a format of DateFormat, if you download a Table containing these values as Data and enable the Table Formatting option, the resulting xlsx file will cause Excel to display an error when you try to open it. In my opinion, this should not happen.
Given that Qlik is storing the value as a date (as verified in the data model viewer) and is displaying it using the default DateFormat pattern (as verified in the Table object), then why can't Qlik properly include this information when generating the formatted xlsx file?
If I put a date() function around the input variable in the load script, even with no format specified; i.e. date(foo), Qlik will properly load the value and when I download the Table as Data with Table Formatting enabled, the Excel error does not occur. Likewise, if I put a date() function around the variable in the Table expression, even with no format specified, when I download the Table as Data with Table Formatting enabled, the Excel error also does not occur.
But I feel it is unreasonable to have to rewrite my Load * statements to explicitly wrap variables in date() functions in order to make Table Formatting work properly. I also feel it is unreasonable to have to wrap fields that are already being stored as dates with the date() function just to make Table Formatting work.
So I guess my question is, when it comes to downloading Tables of dates (with Table Formatting), why should wrapping a variable in date() with no format produce results that are different (better?) than Qlik's automatic conversion of strings to dates using the DateFormat pattern?
