Article Number: 000051853 | Last Modified: 2018/11/21
In QlikView version 11.x and SAP Connector version 5.x, the windows date format didn't superimpose external fields identified as dates. However, in Qlikview versions 12 onwards, the date format in 'SET DateFormat' converts the fields across the document to the specified date format. Because of this, formatting operator Date# on date fields which worked earlier produce null values now.
QlikView version 12.x.
SAP Connector version 5.x and 6.0.
The current behavior of formatting the date to what the SET statement states is the expected one. The earlier behavior per which it didn't is incorrect.
We have the below options:
Modify the SET DateFormat to M/D/YYYY in the script.
Modify the current Date# to just Date(Field, 'M/D/YYYY'). Date# is not required because it may be responsible for introducing a possible error. The values are already stored as DUAL, Date(Field, 'M/D/YYYY') should be sufficient. Modify the date field to a string and then remove the numeric part. This will prevent the SET DateFormat from recognizing it as a date. This is not a very efficient though.
Downgrade Qlikview to 11.x and consider a long term project of upgrading SAP Connector to 7.0 and gradually modify the scripts to comply with the current expected behavior, while serving your purpose.
Note: There is no support of SAP connector version prior to 6.1 and QlikView version 11.x soon reaches end of support. So, compatibility of QlikView version 12.x with connector version 5.x is not known. It is best advised to upgrade both QlikView and SAP Connector to the latest supported version.
Collaborate with over 60,000 Qlik technologists and members around the world to get answers to your questions, and maximize success.