topic Re: Duplicate Issue in QlikView

Duplicate Issue

Wed, 22 May 2013 13:08:44 GMT

I have joined some tables together and notice that i have duplicates: i need to be able to somehow compress these values to only show the instance happening once (because it did not actually occur 3 times). The answer should say 123456 = 75, not 225 which is what it is currently showing me.

Any thoughts?

Item# Final Fcst Qty

123456 75 4

123456 75 6

123456 75 13

Thanks,

Parrish

Re: Duplicate Issue

Gysbert_Wassenaar — Wed, 22 May 2013 13:20:22 GMT

Try using sum(distinct [Final Fcst]) instead of sum([Final Fcst])

Re: Duplicate Issue

Wed, 22 May 2013 13:37:24 GMT

I would stick to average in this case

avg([Final Fcst])

Re: Duplicate Issue

kouroshkarimi — Wed, 22 May 2013 13:46:01 GMT

I'm assuming the Qty field is causing the table to segregate like that. If that table is in the front end then change Qty to sum(Qty).

If it's in the script, then you'll need to introduce a group by clause. e.g.

table a:

load [Item],

[Final Fcst],

sum(Qty)

resident mytable

group by

[Item],

[Final Fcst]

;

Re: Duplicate Issue

Wed, 22 May 2013 13:49:34 GMT

Hi,

Try this

(Answer You get)/Count( [Final Fcst])