Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
In my qlikview, I have a table with dimensions and one expression.
Let's say in dimensions, I have fields A,B,C and a numeric field "amount". In expression, I have sum(amount).
The result of sum(amount) is, for some modalities of B, twice as much as the dimension amount. I don't get why. I know the dimension is the right result but I would like to display a total sum(amount) near the table.
I know you cannot really tell me what is the exact origine of the problem but do you know how I could debug it ? I guess there must be a join which has doubled some lines.
Thanks for your help
May be you are right check your data model once ,
and if possible try to attach your .qvw here..
Your duplicates might be due to your joining keys. If you are not joining on all the keys then you might get duplicate
In the below example, joining keys are Key1 and Key2. But we have not renamed Key3 to Key2 in the second table, so we get duplicates
Tbl:
Load * Inline [
Key1,Key2,Amt
1,1,100
1,2,200
1,3,400 ];
Join(Tbl)
Load * Inline [
Key1,Key3,Amt1
1,1,200
1,2,300
1,3,500 ];
To debug such a thing, place EXIT commands in the script and create a straight-table chart with
- Line_ID (or some other unique key for exactly one line) as dimension
- COUNT([Line_ID]) as an expression
=> That will help you learn if there are really duplicates being created.
Thank you DataNibbler. Very useful
In your script, when creating the final table (and in some intermediate tables as well, but that depends on your script and data model), add RowNo() to your records. This can be used to separate duplicates and deduce the distance of their creation (and probable cause)
What saved me from a horrible QV-death more than once is a construct to dump intermediate tables to QVD's. It goes as follows:
Do not forget to set vDebug back to 0 when you publish your document.
Best,
Peter
Check your outer joins and try aggr as well.
A good QVD monitor/explorer is EasyQlik QViewer by Dmitry Gudkov. Free version available.