topic Re: Why is data doubled up here? in QlikView

Why is data doubled up here?

sarahallen1 — Tue, 06 Dec 2016 15:43:40 GMT

I have reduced data down to one example selection state which gives this issue. So no selections are now needed to replicate this.

I have an expression (for now just in a text box - stripped it right back to what seems to be the root problem):

count(Value*[Weight in Fund])

Which gives a count of 2. I can't see why this is - can't see any multiple rows of non-island data.

Individually, the components of the product are:

count(Value)
count([Weight in Fund])

which both give a count of 1, as expected.

NB I'm actually wanting to do a sum, but I'm just troubleshooting with a count for now.

Reduced QVW attached.

Any help is much appreciated - this is bubbling up to a weighted average style calc in a pivot table and REALLY confusing users when they cross check with Excel. I have no idea why it is doubled up.

Thank you!!

Re: Why is data doubled up here?

vishsaggi — Tue, 06 Dec 2016 15:51:01 GMT

May be try this?

= Count(Value)* Count([Weight in Fund])

= Count(DISTINCT (Value * [Weight in Fund]))

Re: Why is data doubled up here?

vinieme12 — Tue, 06 Dec 2016 15:51:17 GMT

it is because , you Main table (CompanyAdditional) thru which Value is Associated to Weight in Funds has 2 rows of Data for the same value

Try

=count(Distinct Value*[Weight in Fund])

Re: Why is data doubled up here?

sunny_talwar — Tue, 06 Dec 2016 16:05:06 GMT

May be to Sum, you need this:

=Sum(Aggr(Value*[Weight in Fund], File))

Re: Why is data doubled up here?

sarahallen1 — Tue, 06 Dec 2016 16:10:38 GMT

Thank you vinieme12‌ , vishsaggi‌, stalwar1‌ .

Vineeth -

I had not spotted that doubled data in CompanyAdditional. This seems to be the root problem (and explains why, in the non-reduced version of my QVW, I have the problem for some selections and not others).

Vishwarath -

Unfortunately I actually need to do a sum product in a pivot table (expandable to the individual components too). This count is a simplification of where the problem occurs.

sum({$<Value=-{"",'$(null())'}>}Value*[Weight in Fund])

sum({$<Value=-{"",'$(null())'}>}[Weight in Fund])

so I can't use count(distinct...) either, as some of the values might be identical for different rows

Sunny -

I will try using aggr in my function like you suggest, over File - thanks

Will update you all once tested!

I will also look upstream in the data processing to find out why I've got duplicates in CompanyAdditional.

Re: Why is data doubled up here?

sarahallen1 — Wed, 07 Dec 2016 08:51:02 GMT

Found the reason for the duplicates - a dodgy left join. Now resolved.

Thanks all!

Re: Why is data doubled up here?

vinieme12 — Wed, 07 Dec 2016 08:53:34 GMT

Cheers