topic Re: Different results from WinSQL and Qlikview for the exact same query? in App Development

Different results from WinSQL and Qlikview for the exact same query?

Matty — Fri, 15 Nov 2024 21:06:07 GMT

Hi,

I have a Qlikview model that extracts data from an ERP system database via ODBC. However, the data returned was inconsistent with what was expected. So I isolated the query in question and created a new Qlikview model with only this query and no conversion or anything and just presented the returned values in a straight table, which I then exported to an Excel document to review.

What I found was that the exact same query in WinSQL directly querying the database gave a different result to the query in the load script of my QV model. There were records missing in the QV model. At first I thought maybe there was duplicates that QV merged somehow, but even when adding a DISTINCT in my WinSQL query, it is still the same.

The Qlikview version is 11.20.12904.0

Any ideas why this could be?

Re: Different results from WinSQL and Qlikview for the exact same query?

marcus_sommer — Tue, 23 Jan 2024 11:37:02 GMT

It's probably not the load which excludes duplicates else it's the UI object which always showed distinct values.

To track it I suggest that you add a record-id and if none exists to create ones with recno() and rowno() within the Qlik load (not within the SQL part else within a separate load or a preceding load). Within the UI add both record counter-fields in the object and you could see which records are really there.

Re: Different results from WinSQL and Qlikview for the exact same query?

Matty — Wed, 24 Jan 2024 00:03:59 GMT

I figured it out.

Apparently a straight table is only showing unique rows and not ALL rows, so therefore records were missing.

When I used a pivot table instead I got the correct results.