Qlikview Seems to be removing duplicate entries

[jasonwills]

Hi All,

i think i'm going slightly mad on this one, and connot work it out. Please bear with me, as i will hopefully explain it OK.

I am loading information from SQL as below into a QVD file, and the loading the data from there, all good.

LoadIntoQVD:
Sql SELECT
b.partnumber as PartNumber,
b.price as Price,
b.quantity as Qty,
b.originalprice as OriginalPrice,
b.countrycode as CountryCode,
b.regioncode as RegionCode,
b.DateInvoiced,
b.invoicenumber as InvoiceNumber,
o.delname as DeliveryName,
o.ordernumber as OrderNumber,
o.projectname as ProjectName
FROM
BaseStatistic_T as b,
OrderTransaction_T as o

WHERE (b.invoicenumber = o.invoicenumber) and (b.DateInvoiced >= '2017-01-23 00:00:01' and b.DateInvoiced <= '2017-01-23 23:59:59');
STORE
LoadIntoQVD into Test.qvd;

If i run the same SQL query in SQL Management Studio, i get 6 results, However, in Qlikview i only get three results - looking at the data, it looks like it is 3 results duplicated. I would have thought that i would still get 6 back in Qlikview, as i am not using any distinct calls or anything.

p.s. - if i also use the same query for Power BI, i get the expected 6 results.

Does anyone have a pointer as to where i can look  for this anomoly?

Thanks in advance

Jason

[marcus_malinow]

Hi Jason,

this is expected behaviour - Qlik will only show distinct records. If you have a field, maybe a RowNo(), that differs in your duplicated rows then that would force the display of the duplicates.

Marcus

[marcus_malinow · Accepted solution]

Just to clarify, under the hood the additional rows will be being loaded into your appication - if you go into the table viewer (Ctrl T) and hover over the header bar of your table, you can see the row count, or you could create a COUNT expression which would highlight these also.

[jasonwills]

Ah, OK, thanks Marcus - i thought i read somewhere on here that it wouldn't.

My mistake, i'll create a unique RowNo() and see how i get on.

Many Thanks

Jason

[ravikumar_iyana]

Everything seems to be correct. You have to be aware of that if you have other dimensions they will create uniqueness for each row.

It is the combination of all dimensions in a chart that determine uniqueness. So what you see is very logical to me and conforms to how QlikView works and should work.

If I create a new chart with ONLY the OrderNumber and Issue Number and the count the repeating/duplicated values for Issue Number disappears.It is the inclusion of PSRI_ID and Item Number that force a duplication in your two charts - that is how it should be..

or

In Visualization, QlikView will always show you distinct value, but in backend the values are always same as what you have loaded.

To make sure that you have the same numbers of record, you can go to Settings-> Document Properties -> Table -> Here you will see the numbers of records loaded.

[jasonwills]

Guys, thanks for all the input, you have lead me to the right place.

Thankfully i am in control of the SQL script, so added newId() as ID to the script, so now i have a unique ID on the data.

Many Thanks

Jason