topic Re: Double sum of OrderLineValue in Linktable datamodel in QlikView

Double sum of OrderLineValue in Linktable datamodel

thymenkristen — Wed, 01 Sep 2021 14:20:28 GMT

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!

Re: Double sum of OrderLineValue in Linktable datamodel

Strooprover — Fri, 03 Sep 2021 09:55:25 GMT

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

Re: Double sum of OrderLineValue in Linktable datamodel

marcus_sommer — Sun, 05 Sep 2021 13:20:42 GMT

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

Re: Double sum of OrderLineValue in Linktable datamodel

SanderHovestad — Thu, 09 Sep 2021 14:31:49 GMT

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

Re: Double sum of OrderLineValue in Linktable datamodel

marcus_sommer — Thu, 09 Sep 2021 15:10:50 GMT

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

Re: Double sum of OrderLineValue in Linktable datamodel

SanderHovestad — Fri, 10 Sep 2021 08:26:43 GMT

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?

Re: Double sum of OrderLineValue in Linktable datamodel

marcus_sommer — Fri, 10 Sep 2021 08:39:02 GMT

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

Re: Double sum of OrderLineValue in Linktable datamodel

SanderHovestad — Fri, 10 Sep 2021 09:05:26 GMT

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 🙂

Re: Double sum of OrderLineValue in Linktable datamodel

marcus_sommer — Fri, 10 Sep 2021 09:16:41 GMT

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

Re: Double sum of OrderLineValue in Linktable datamodel

SanderHovestad — Mon, 13 Sep 2021 11:22:50 GMT

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?

Re: Double sum of OrderLineValue in Linktable datamodel

marcus_sommer — Mon, 13 Sep 2021 11:58:23 GMT

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

Re: Double sum of OrderLineValue in Linktable datamodel

SanderHovestad — Tue, 12 Oct 2021 10:14:13 GMT

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.