Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
thymenkristen
Contributor III
Contributor III

Double sum of OrderLineValue in Linktable datamodel

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.

thymenkristen_0-1630502533872.png

 

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!

 

1 Solution

Accepted Solutions
SanderHovestad
Contributor II
Contributor II

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.

View solution in original post

11 Replies
Strooprover
Contributor
Contributor

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II

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
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II

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
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II

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

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

SanderHovestad_0-1631264291929.png

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:

SanderHovestad_1-1631264355184.png

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

SanderHovestad_1-1631264650061.png

I'm totally lost here 🙂

 

 

marcus_sommer
MVP & Luminary
MVP & Luminary

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
Contributor II
Contributor II

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).

SanderHovestad_0-1631532050901.png

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?