I’ve been looking into a massive data anomaly between figures reported in a Qlikview document and our front-end system and have discovered this was being caused by the Microsoft OLE DB Provider for ODBC Drivers. The System DSN in this case is using an Informix driver, but if I use this ODBC connection directly, the figures returned are correct. When testing the provider in Excel, it fails with an error, but Qlikview is not reporting a failure, instead returning figures as explained later.
I understand this provider is deprecated by Microsoft, so am advising all my developers to use the ODBC connection, but wanted to ask if any users had experienced anything similar.
And now the problem. Imagine a data table as this in the backend …
Item Estimate1 Estimate2 Estimate3
A 100 0 23
B 150 123 0
C 0 0 0
D 0 0 26
E 12 1 125
F 0 2 0
When using the OLEDB provider, Qlikview is ignoring the zero values and instead returning the last loaded value in that field giving …
Item Estimate1 Estimate2 Estimate3
A 100 0 23
B 150 123 23
C 150 123 23
D 150 123 26
E 12 1 125
F 12 2 125
I suspect it is because of issues with Nulls in the backend, but the worrying aspect is that Qlikview is unable to report any problem. If the figures hadn't been so widely different, this could have gone unnoticed.
I then decided to test against another ODBC connection (in this case some file based FoxPro databases) and, although have no issues like above, do see TRUE/FALSE flags handled differently – the provider returns -1(true)/0(false), whereas ODBC returns 0(false)/1(true).
Anyone got any comments?
flipside
