topic GrossWeight in QlikView

GrossWeight

Wed, 01 Sep 2010 11:54:16 GMT

Hi,

i have a qlikview document which consolidates orders for example customer a has ordered 2 items which are the same on the same day but with different order numbers

when i produce the table qlikview consolidates these jobs which is fine but i want it to give me the total weight not the consolidated weight ? i hope this makes sense?

at the moment it is removing the *duplicate weight* and only giving me the weight of one for example 1kilo instead of 2

thanks for your time

Jase

GrossWeight

Wed, 01 Sep 2010 12:05:23 GMT

it might help if you showed some data. Is the weight numeric, it can only sum numbers, not strings.

GrossWeight

Wed, 01 Sep 2010 12:16:58 GMT

Hi Steve

the Weight is a numbers and not strings if you can see below:

Date

Dcity

DDepot

DID

Dname

Division

GrossWeight

Module ID

Oarea

ODepot

Oname

Ozip

Priority

Volume

#############

Birmingham area

CoP Birmingham

test

3402.680814

6.1.2

LES

Test Company

DL5 6EP

Collection

16.98775

#############

Birmingham area

CoP Birmingham

test

3402.680814

6.1.2

MANCHESTER

Test Company

DL5 6EP

Collection

16.98775

#############

Daventry

CoP Daventry

PDC Daventry

test

3402.680814

7.1.2

LES

Test Company

DL5 6EP

Collection

16.98775

#############

Daventry

CoP Daventry

PDC Daventry

test

3402.680814

7.1.2

MANCHESTER

Test Company

DL5 6EP

Collection

16.98775

the table produces

CalendarYear	CalendarMonthName	CalendarDayName	Jobs	Weight	Volume
2009	May	Mon	2	6,805	34

but if you look at the original data and add the weight up and volume it is treating it as duplicate data

hope this makes sense?

GrossWeight

Wed, 01 Sep 2010 12:16:59 GMT

Hi Steve

the Weight is a numbers and not strings if you can see below:

Date

Dcity

DDepot

DID

Dname

Division

GrossWeight

Module ID

Oarea

ODepot

Oname

Ozip

Priority

Volume

#############

Birmingham area

CoP Birmingham

test

3402.680814

6.1.2

LES

Test Company

DL5 6EP

Collection

16.98775

#############

Birmingham area

CoP Birmingham

test

3402.680814

6.1.2

MANCHESTER

Test Company

DL5 6EP

Collection

16.98775

#############

Daventry

CoP Daventry

PDC Daventry

test

3402.680814

7.1.2

LES

Test Company

DL5 6EP

Collection

16.98775

#############

Daventry

CoP Daventry

PDC Daventry

test

3402.680814

7.1.2

MANCHESTER

Test Company

DL5 6EP

Collection

16.98775

the table produces

CalendarYear	CalendarMonthName	CalendarDayName	Jobs	Weight	Volume
2009	May	Mon	2	6,805	34

but if you look at the original data and add the weight up and volume it is treating it as duplicate data

hope this makes sense?

GrossWeight

Wed, 01 Sep 2010 12:32:14 GMT

I assume that's a straight table. You have summarsied by day, so if there are 2 jobs , 6805 looks right. What are the dates - is it all the same date? What expression have you used tosum the weight?

Steve

GrossWeight

Wed, 01 Sep 2010 12:38:16 GMT

Hi Steve,

6805 isnt correct its only adding together 2 of the 4 jobs its all the same date the only difference between the jobs is the Dcity 2 are going to birmingham and 2 to Daventry.

but my Pivot table is only taking 2 jobs and not the 4 ?

does this make sense? 😜 on the weight field on the pivot table im using just Sum (GrossWeight)

GrossWeight

deepakk — Wed, 01 Sep 2010 12:47:44 GMT

HI Westie,

It seems there is some problem in your data modelling. Such error occurs when the linking between tables are wrong.

If you can show the table and the linking we might able to help you out.

GrossWeight

llauses243 — Wed, 01 Sep 2010 12:50:57 GMT

Hi,

to test aggr(nodistinct Sum (GrossWeight))

good luck, Luis

GrossWeight

Wed, 01 Sep 2010 13:03:35 GMT

Hi Luis,

i tired that and it gives me a - in the gross weight field?

GrossWeight

Wed, 01 Sep 2010 13:57:28 GMT

Hi Deepak,

i cant give you the data as its customer data otherwise i would.

GrossWeight

Wed, 01 Sep 2010 14:09:23 GMT

It looks like the problem is your table box is generating extra rows which aren't really part of your data.
I suspect your table box is showing data from more than one table

the sum() will only sum actual data, not data created by joining data in a table box.

e.g. if you have data like this:

fact_table:

DIM, data

A,1

A,3

B,2

dim_table

DIM, desc

A, blah

A, blah2

B,blah3

If you show a table box you will see:

DIM, desc,data

A,blah1,1

A,blah2,1

A,blah1,3

A,blah2,3

B,blah3,2

but if you show a pivot with DIM as the dimesion you will get

DIM, sum

A, 4

B,2

This is correct, the sum of data for DIM=A is 4, not 8. If you really wnat to count the sum of the generated data, you could use a join in your adta load to create a table that looks like your table box.

Steve