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

[Matty]

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?

[marcus_sommer · Accepted solution]

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.

[Matty · Accepted solution]

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.