The sum of a field over a dimension does not give the correct output.

[cheburashka]

Hello,

I have a straight table where I have one expression (a sum) and multiple dimensions. The sum does not give the answer it should give.

Is this a bug in Qlikview?

Please take a look at the attached qvw for a more detailed description of the question.

_Koen

[Gysbert_Wassenaar · Accepted solution]

Looks like you have duplicate records. Add an expression count([# of hours]) to the same chart. You'll see a count of two for the second and third last lines. That means you have two records with the same values. Qlikview shows only one row for duplicate records.

[cheburashka]

Oke I tried that.

But why is there only one record visible in the "All Data" table box?

I guess you mean that "Qlikview shows only one row for duplicated records" ?

In the table viewer > preview I can indeed see that there are duplicated records.

Why are there duplicated records? Can I remove them with an extra line in the script?

Thx for your advice.

_Koen

[Gysbert_Wassenaar]

My guess would be that the duplicates are caused by one of the joins somewhere or perhaps by the intervalmatch. You could do a load distinct on the resulting Master data table so you have a deduplicated table. But I think you should look at the joins first. There may be other problems hidden in there. Better ot make sure that the loads/joins are correct imho.

[hic]

I agree with Gysbert Wassenaar - check that the joins are correct.

However, I would take it one step further: You should question the joins altogether. They might be totally unnecessary, and should then be removed.

Read more on To Join or not to Join.

HIC

[cheburashka]

I went for the easy solution.

I wrote the following line to remove the duplicated record lines.

Load distinct * Resident [Master data];

I have no idea  what I should look for when I check the joins and intervalmatch.

It is the first join I ever did, and I used the script Gysbert Wassenaar advised me in a earlier question.

For now it is working .

Thx for your advice

_Koen

[cheburashka]

I've read your blogpost. In this Qvw the JOIN was used because it was part of the solution for my question on Re: How to built a report table depending on whether two periods overlap?. I'm considering of not using a JOIN statement. But I need a working intervalmatch though.

Is it possible to do a interval match between a date of one table and a interval which is part of a different table?

How will the date and the interval be linked if you dont JOIN the 2 tables?

_Koen

[hic]

The intervalMatch creates a table with exactly three fields: Date, IntervalStart and IntervalEnd. And with these fields it becomes a bridge table that links the date table and the interval table.

An alternative to intervalmatch is a while loop:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

HIC