topic Find Out why values in Pivot are doubled in QlikView

Find Out why values in Pivot are doubled

Fri, 19 Jun 2009 14:14:06 GMT

Hello,

I'm having a pivot-chart where a value, that is defined as SUM(ISNULL(BAAMOUNT),0,BAAMOUNT) takes the double of what it should be, i've added every non-numeric value in the QV as a dimension (to find out which is causing the double) but i can't find it.

How do i trace somethign like this then ?

Find Out why values in Pivot are doubled

Fri, 19 Jun 2009 14:25:54 GMT

Maybe it has somethign to do with this ? This is how the data is build

ANOBAL:
SQL
SELECT '$(vCmp)/' || ANOBAL.BAREFK AS C_KEREFK,
ANOBAL.BAYEAR AS ASW_YEAR,
XM.MONTH_NBR AS ASW_MONTH,
CASE XM.MONTH_NBR
WHEN 1 THEN BADA01
WHEN 2 THEN BADA02
WHEN 3 THEN BADA03
WHEN 4 THEN BADA04
WHEN 5 THEN BADA05
WHEN 6 THEN BADA06
WHEN 7 THEN BADA07
WHEN 8 THEN BADA08
WHEN 9 THEN BADA09
WHEN 10 THEN BADA10
WHEN 11 THEN BADA11
WHEN 12 THEN BADA12
ELSE BADA13
END AS BADA,
CASE XM.MONTH_NBR
WHEN 1 THEN BACA01
WHEN 2 THEN BACA02
WHEN 3 THEN BACA03
WHEN 4 THEN BACA04
WHEN 5 THEN BACA05
WHEN 6 THEN BACA06
WHEN 7 THEN BACA07
WHEN 8 THEN BACA08
WHEN 9 THEN BACA09
WHEN 10 THEN BACA10
WHEN 11 THEN BACA11
WHEN 12 THEN BACA12
ELSE BACA13
END AS BACA
FROM $(vDb).ANOBAL ANOBAL
CROSS JOIN (SELECT 1 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 2 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 3 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 4 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 5 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 6 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 7 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 8 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 9 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 10 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 11 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 12 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT 13 AS MONTH_NBR FROM SYSIBM.SYSDUMMY1) XM
WHERE ANOBAL.BASUTA = 'A11'
AND ANOBAL.BAYEAR >= 2008;
LOAD C_KEREFK, ASW_YEAR, ASW_MONTH, (BADA+BACA) AS BAAMOUNT RESIDENT ANOBAL;

And this is the result .......

If i take on the same 'pivot-line' the a value that comes out the db directlty is OK, when i take my recalculated value ......

Find Out why values in Pivot are doubled

stephencredmond — Fri, 19 Jun 2009 15:45:32 GMT

Hi,

When my values are duplicating like this, it is generally due to one of two things:

1. Duplicate values in the source data. QlikView can sometimes hide this from you. I will use RowNo() in the load script to generate a unique ID for each row to check this.

2. Bad association. Perhaps associating on 1 field when it should be associating on 2.

Regards,

Stephen

Find Out why values in Pivot are doubled

Fri, 19 Jun 2009 20:10:03 GMT

Uh ......

Find Out why values in Pivot are doubled

Sat, 20 Jun 2009 02:55:41 GMT

Nobody has a suggestion ???

Find Out why values in Pivot are doubled

Anonymous — Sat, 20 Jun 2009 03:32:14 GMT

It may happen if you're joining tables with one-to-many relationsships into one logical table in QV. Try using aggr() in your pivot table, probably by XRENCO, but I can't tell for sure, maybe by something else:
aggr(<your current expression>, XRENCO)

Find Out why values in Pivot are doubled

rwunderlich — Sat, 20 Jun 2009 06:34:37 GMT

Is that a table box you are displaying? If you have duplicated the rows somewhere, QV will olny display one row for each set of unique values.

Try creating A Straight Table Chart that looks like this:
Dimension: XRECNO
Expressions:
=sum(BAAMOUNT)
=count(BAAMOUNT)
=count(XRECNO)

-Rob