Double sum of OrderLineValue in Linktable datamodel

[thymenkristen]

Dear all,

We are encountering a problem which we can’t get a grasp on. Currently we are working on a data-model in Qlikview which makes use of a so called: Linktable. The way we built up de linktable is as follows:

LinkTable:
LOAD DISTINCT
FactuurRegelKey,
ProductKey,
DebiteurKey,
OrderRegelKey,
'VF' as TYPEPeriode,
FactuurDatum as PeriodeKey,
Intercompany,
RepLevelKey,
VestigingsCode,
AdministratieNummer,
AccountManagerNaam
RESIDENT f_Facturen;

This is repeated for every Fact_table. By including the TYPEPeriode in the set-analysis in all variables we use, we get the correct and expected result. I.e. in the case of the invoice amount, we sum({<TYPEPeriode={‘VF’}>}InvoiceLineValue) and get the result we would expect.

However in the case of the OrderLineValue, we have the following expression 1:

=sum({<TYPEPeriode={'VB'}, OrderDate={"$(='>=' & Date(YearStart((vMaxDate_OrderDate))) & '<=' & Date((vMaxDate_OrderDate)))"}>} OrderLineValue)

We would expect an outcome of €132.30, which is the actual fact.
But it is giving us €264.60.
Exactly the double of the expected amount.  

And this despite the fact that TYPEPeriode = ‘VB’ is included in the set analysis. We would expect that with this expression we would only get the OrderLineValue associated with the TYPEPeriode = ‘VB’.

See the image below, I selected the OrderRegelKey (OrderLineKey) and I get back 5 rows in the Linktable. However, I get 2 rows with unique keys in the f_Facturen and 1 row in f_Orders. The sum of the OrderLineValue is based on a field only present in the f_Order table.

So my question:
Why do I get a double sum of the amount on the OrderLineValue? Is it ignoring my TYPEPeriode field in the set analysis or is there another explanation?

If I construct the expression 2 as follows:

=sum({<TYPEPeriode={'VB'}, OrderDate ={"$(='>=' & Date(YearStart((vMaxDate_ OrderDate))) & '<=' & Date((vMaxDate_ OrderDate)))"}>}aggr( OrderLineValue, OrderRegelKey))

I get the correct result, however I do not understand why.
Can someone explain this?

Thanks in advance!

[marcus_sommer]

I think there is at least one other table which is associated within this view which has duplicate values or caused the duplication because of the way how the key looked like on which the table is linked. Maybe you take the table from TypePeriode as the next.

Before doing this make sure that there is no synthetic key or any circular loop within the datamodel.

- Marcus

[SanderHovestad · Accepted solution]

All,

Just wanted to let you know that we have been able to solve the issue. It was indeed caused by a duplicate key, caused by  a LEFT JOIN to the invoice table. The only way to find this out, was by bring the Qlik load script down to the bare minimum and one by one add the tables back to the model. This way we found the query that gave the error.