topic Re: record count is different when stored in qvd in QlikView

record count is different when stored in qvd

dhirajkhodade — Sat, 16 Nov 2024 01:34:14 GMT

I am trying to use left join in qlik view like given below

Trades:
LOAD

a,b,c,d

From abc.qvd (qvd); //it have 85000 records

left Join (Trades)
LOAD
a,z

From xyz.qvd (qvd); //it has 3,00,000 records which has all the records from abc.qvd

Store Trades into final.qvd;

When I put Trades table in table object on qvw UI it gives me expected result that is all the records from abc.qvd along with new field i.e. z from xyz.qvd and total record count is 85000

but when I load all the records from final.qvd in another qvw I am getting some weired number of records which are not equal to 85000 or 3,00,000

why is this happening ??

Re: record count is different when stored in qvd

jonathandienst — Wed, 08 Jan 2020 06:47:30 GMT

If the values of the associated field (a) are not unique in xyz.qvd, then that will cause rows to be replicated when joining. The left join means that only the records that have an existing a value (from abc.qvd) will be loaded.

So if you have non-unique values of a in xyz.qvd, you can expect to get a final result of at least less than 85k rows and not more than 3 084 999 rows.

Re: record count is different when stored in qvd

dhirajkhodade — Wed, 08 Jan 2020 06:55:33 GMT

Thanks for quick response @jonathandienst
But that still does not explain why I am seeing different number of records when I put same table in table object on sheet/UI vs when I store it in another qvd i.e. final.qvd.

It should have same number of records in both cases right? correct me if I am wrong.

Re: record count is different when stored in qvd

Arthur_Fong — Wed, 08 Jan 2020 08:00:16 GMT

Is there any tables with the same columns in final.qvd table?

I think your final table has auto-concatenated with another table in your final qvw.

To avoid this, try:

noconcatenate

Final:

load *

from final.qvd;

Re: record count is different when stored in qvd

dhirajkhodade — Wed, 08 Jan 2020 08:02:42 GMT

No there are no tables other than final.qvd data because I am loading it in new qvw for testing.

Re: record count is different when stored in qvd

Arthur_Fong — Wed, 08 Jan 2020 08:09:03 GMT

Can you try ApplyMap instead of left join?

Re: record count is different when stored in qvd

Brett_Bleess — Wed, 05 Feb 2020 21:00:28 GMT

Just wanted to give you a Design Blog post on what Arthur mentioned that may be of some help:

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-join-use-Applymap-instead/ba-p/1467592

Regards,
Brett