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!

[Strooprover]

I have a similar problem.
Anyone a idea what is happening here?

[marcus_sommer]

It seems that either the keys to the link-table aren't created properly and/or that there are duplicated values - maybe some of the merging load-processes aren't distinct. To check if there are duplicates you need to ensure that you have an absolutely unique record-id because all the UI objects in Qlik will display only the distinct combinations of the used fields and you wouldn't see the duplicates. If no such unique id exists you could create one with rowno().

- Marcus

[SanderHovestad]

Hi, 

My colleague Thymen above opened this topic. I've done some testing and debugging but I am still not able to retrieve the cause of this. A Table Box shows only 1 record, where as a count(OrderLineValue) shows that there are 2. Is there a way to further dive into this, by showing system tables or hidden fields?

Regards, Sander

[marcus_sommer]

System-tables and/or hidden fields aren't helpful for such use-cases else like above mentioned an unique record-identifier is needed. If none such key exists respectively through various merging-measures like concatenate or joins they aren't unique anymore you could create them with recno() and/or rowno() within all relevant tables. Further helpful is often to put an appropriate source-information within all concatenated tables.

If you now adds the recno(), rowno() and source within the tablebox with your other fields you will see the duplicated data and from where they are und usually this leads quite soon to the why.

- Marcus

[SanderHovestad]

Thanks for the hint, however this is not giving any more insights. The straight table still shows that there's 1 record in the Linktable, and the  count(OrderLineValue) still shows 2. My gut feeling says the reason that is causing this, is because for this orderline we have 2 invoices. But still, by filtering on TYPEPeriode = VB we exclude the invoice lines.

Any more ideas?

[marcus_sommer]

If you add the mentioned rowno() and source as dimension to the straight-table you will quite probably see that there is more as one record.

Each UI object in Qlik will always display the distinct combination of field-values and therefore you need the record-identifier there.

- Marcus 

[SanderHovestad]

Hereby the screenshots, which to me give not any more insights.

Below are all Linktable rows, including rowno() and recno()in a Table Box:

As you can see the last column contains the same value for every TYPEPEriode, which is the correct one.

When filtering only on the VB TYPEPeriode, there is 1 row remaining with again showing the correct amount:

But unfortunately a sum of this column shows the amount * 2 even when including the rowno() and recno() in the straight table:

I'm totally lost here 🙂

[marcus_sommer]

I suggest to put also a rowno() within the table(s) from which the measures [OrderRegelNettoB...] comes and pulling them also within the straight-table.

- Marcus

[SanderHovestad]

Unfortunately no progress on this, despite adding the recordno's.

In the screenshot below you see the single orderline being invoiced twice, where the OrderRegelKey and the OrderRegelRecNo is the same for all records, but is different for the two invoices (TYPEPeriode VF).

I also included 3 textboxes that show the counts of the keys and the amount when filtering on TYPEPeriode VB, so you can see we have 1 orderkey, 0 invoice keys but still 2 amounts. Any idea?