duplicate values

[yelin_nyu]

Hi,

I have to debug my boss's code. he didn't have load distinct in his script, but the end result is qty (A)= 4 rather than 8. There are so many left joins and resident tables involved. I wonder how you could possibly lose that hidden record without use load distinct.

I have a qvd with following table

product  site qty

A              1   4

A               2   4

B               1    12

when I do

load product,

qty

from tbl;

(I didn't do load distinct cuz I don't want to lose the qty)

I will get this in table view

product qty

A             4

B              12

but if I do a chart to sum the qty, I should get

A  8

B  12

but my boss's result is

A 4

B 12

how did he lose that extra record? even if it's not displayed but still should sum up to 8.

[#2]

Couple of suggestions:

- You're talking about many joins within the script. Maybe this table is joined and 'site' is part of the key. It's possible that site = '2' isn't part of the original table. If so, that record will not be joined to the original table.

- Product name isn't identical. It's possible that the product names look very much alike, bit aren't similar.

- (Hidden) conditional statement on the table. Maybe the record (where site = 2, value = 4) isn't added to the result because only records with site=1 are taken into account.

[yelin_nyu]

sorry, I didn't provide more detail last time. assume following situation:

item plant qty program

A     B       4    X

A     B       4    Y

I only load in item plant & qty into this tool

so the table view is:

main:

item plant qty

A     B      4

but if I do chart:

item plant sum(qty)

A     B       8

now, I left join this table (1 row)

item plant New

A     B         Y

onto main to become below

item plant qty New

A     B      4     Y

But when I tried to sum qty in chart, this is what I got

item plant sum(qty)

A      B         4

what happened to that hidden row before I did the join?

[Gysbert_Wassenaar]

A table box will only show unique rows. If you don't add the program field to the table box then the combination A, B, 4 is the only unique combination. That combination will be shown only once, so the table box shows only one row. For what you want you should not use a table box but a straight table with item and plant as dimensions and sum(qty) as expression.

[yelin_nyu]

I want to understand why the hidden row got dropped after the left join. I'm fine with table view only display one row as long as the sum(qty) is right. But in my case the sum(qty) become wrong after I left join a new flag onto the table. i am positive the left join caused the hidden row to drop.

before join I did the sum(qty)=8

after the join the sum(qty)=4