topic problem with 2 tables in QlikView

problem with 2 tables

ecabanas — Wed, 15 Jul 2015 13:55:03 GMT

Hi all

I hve a different result reading the same data, I'm stuck and I need your fresh help

many thank's

Eduard

Re: problem with 2 tables

morganaaron — Wed, 15 Jul 2015 13:59:50 GMT

Eduard,

Can you give us more of an explanation? What data is reading incorrectly? What's the expected output? Not a lot to go on at the minute!

Re: problem with 2 tables

ecabanas — Wed, 15 Jul 2015 14:04:01 GMT

If you open the doc you will see 2 tables:

a- Units fron transactionId: sum correctly: 9 units

b- Units from itemid:Sum incorrectly

both read the same info and I do not know why the units are different

thank's

Re: problem with 2 tables

stigchel — Wed, 15 Jul 2015 14:14:26 GMT

It's different because there are multiple distinct values for Unidades which are being summed in table b but not in table a. If you want table b to display the same use

sum(DISTINCT UNIDADES)

Re: problem with 2 tables

ecabanas — Wed, 15 Jul 2015 14:22:12 GMT

HI Piet,

I rechecked and the answer is not correct,please uncheck inventransid selection, just only itemid and the sum still is not correct

! Many thank's

Re: problem with 2 tables

stigchel — Wed, 15 Jul 2015 14:41:55 GMT

Can you try with

Sum(Aggr(sum(DISTINCT UNIDADES),INVENTTRANSID))

Re: problem with 2 tables

morganaaron — Wed, 15 Jul 2015 14:44:31 GMT

Hi,

There are multiple values for UNIDADES for each INVENTTRANSID. If you replace UNIDADES with Sum(UNIDADES) in your first table, you'll see your values quadruple - it looks like you have 4 values for every ID, therefore -36 is naturally four times higher than your -9 value when only summing one lot of the values.

Re: problem with 2 tables

ecabanas — Wed, 15 Jul 2015 14:55:44 GMT

hI aARON,

The Inventtransid is an unique field, there is not many units for each inventtransid.

thank's

Re: problem with 2 tables

morganaaron — Wed, 15 Jul 2015 15:18:29 GMT

Hi Eduard,

Not to drag this out, but there are.

This is taken from the table viewer (i.e. the underlying table your two values are coming from):

Have a look at your first four lines - they all have the same INVENTRANSID of 00538094-103. This is the same for all your values, there are 4 rows for every 'unique' line, therefore your values are being quadrupled.

Sort that, sort your problem.