Numbers doubling up

[paul_ripley]

Hello

I have a simple report that extract data from the database.

I filter on one period, asset and account code and thr amount 1698 is correct in the table box

When use the expression sum(amount) it doubles the value to 3397.

Do you have any idea why this may be?

I attache the qvw

Many thanks

Paul

[lironbaram]

usually it means you have duplicated records 

[tresesco]

Qlikview table box shows only unique combination of records. So that might not help you get the right picture here. Try 'show frequency' in amount listbox; you would see 2 as frequency for your selected record.

[Victor_Alumanah]

Hi Paul,

The reason why your figures are doubling up is because you have loaded the data in twice, I would suggest reviewing your script to find out what has caused it, or even better clear the report of any data and reload to see how many rows are loaded.

At the moment you can see 148,437 rows, but there are actually 296,872 rows(double). Go to table viewer and hover over the FixedAssets table and you will see the 296,872 number of rows displayed.

I hope this is helpful.

Victor

[sunny_talwar · Accepted solution]

Did you forget to add the condition on Year may be? because without a condition on Year, you might be looping over 2 years pulling the same data twice....

  SELECT k.client,
         k.asset_id,
         a.description AS asset_descr,
         a.date_from,
         k.asset_group,
         k.dim_1,
         c.description AS asset_group_desc,
         k.trans_type,
         k.voucher_type,
         k.account_type,
         k.period AS gl_period,
         k.trans_date,
         SUM (k.amount) AS amount,
         SUM (k.value_3) AS value_3,
         k.account
    FROM aatasset a, agldescription c, aattrans k
   WHERE     k.client = a.client
         AND k.asset_id = a.asset_id
         AND a.status = 'N'
         AND k.client = c.client
         AND a.asset_group = c.dim_value
         AND c.attribute_id = 'G10'
         AND year = '$(vYear)'
GROUP BY k.client,
         k.asset_id,
         a.description,
         a.date_from,
         k.asset_group,
         k.dim_1,
         c.description,
         k.trans_type,
         k.voucher_type,
         k.account_type,
         k.period,
         k.trans_date,
         k.account
ORDER BY asset_group_desc, k.asset_id;

[paul_ripley]

Thanks , i think it is something to do with that

[paul_ripley]

Thanks, that is the reason ie that i am picking up the year twice. I will try to work out how to fix it