Simple calculation error - I guess - but where?

[datanibbler]

Hi,

I have a very strange phenomenon.

I have two listboxes with the same field in two different (but linked) tables - not the keyfield, but a separate one - I have loaded the same field twice in every table, linking on one of the two copies, so I can properly count the other one.

When I go like >> COUNT({1} [field] <<, I get two different figures, I can calculate the difference.

However, when I do the following

- in the listbox with the fewer items, "select all available"

- in the listbox with more items, "select excluded"

- COUNT([field]

=> I get another number, about 700 more i´n this case.

Do you have an idea why I get this discrepancy?

Thanks a lot!

Best regards,

DataNibbler

[Gysbert_Wassenaar]

If you load a field in two different tables with the exact same name then that field will be used to link the tables and it will be a key field. And you should not use a key field in the count function.

But perhaps I'm misunderstanding you. Can you post a small qlikview document that explains your problem?

[avinashelite]

I am not clear whether your counting on the key field or on the other filed which you have created for the counting purpose, as Gysbert mention you cannot base your count on the linking key and compare it which the field created for the counting purpose because key will have the count of both keys i.e the keys from the table an table and it need not be common and need on be DISTINCT also ....and it will definitely very with the count ..base your count on the other field

[datanibbler]

Hi you two,

sorry if I wasn't clear on that point.

I know that counting on a keyfield that is used to link tables is not a good idea.

For that reason I created a copy of it in the LOAD and based my count on the copy.

Well, in theory it might well be that there is a difference of, say, 5 items between the tables - if one table is effectively bigger than the other - and in addition to that, there may be items (say 3) which do not match between the tables.

That would lead to a discrepancy like the one I've noticed.

The queer thing is, the reason for the discrepancy that I've found leads me to believe that all these items that do not match (the bigger number, the 8 in the example) are simply not contained in the other table. That is what confuses me.

Thanks a lot!