Tip to debug a mistake

[#1]

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

[sasikanth]

May be you are right check your data model once ,

and if possible try to attach your .qvw here..

[anbu1984]

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 ];

[datanibbler]

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.

[#5]

Thank you DataNibbler. Very useful

[Peter_Cammaert]

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:

1. Create a variable, say vDebug
2. Put the following after each doubtfull LOAD (change the table name and file name to keep different stage QVDs):
   
   IF (trim('$(vDebug)') = 1) THEN
     STORE ThisTable INTO [.\ThisTable.QVD] (qvd);
   END IF
   
   
3. In Settings->Variable Overview, set vDebug to 1 whenever you want to have a look inside the Transformation step.
4. Use a QVD monitor to check the content of the QVD in each stage
   
   

Do not forget to set vDebug back to 0 when you publish your document.

Best,

Peter

[#7]

Check your outer joins and try aggr as well.

[Peter_Cammaert]

A good QVD monitor/explorer is EasyQlik QViewer by Dmitry Gudkov. Free version available.