topic Re: duplicate values in QlikView

duplicate values

yelin_nyu — Mon, 30 Sep 2013 01:34:02 GMT

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.

Re: duplicate values

Mon, 30 Sep 2013 11:54:25 GMT

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.

Re: duplicate values

yelin_nyu — Tue, 05 Nov 2013 18:49:37 GMT

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?

Re: duplicate values

Gysbert_Wassenaar — Tue, 05 Nov 2013 18:57:30 GMT

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.

Re: duplicate values

yelin_nyu — Tue, 05 Nov 2013 19:07:42 GMT

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