topic Sum based on different columns in QlikView

Sum based on different columns

jharke — Wed, 25 Nov 2020 16:16:04 GMT

Hello Community

In short I have a table with cities FROM and TO and the amount of goods that is transported between these cities:

PlaceFrom	PlaceTo	Goods
A	B	20
C	D	20
B	A	30
E	D	30

Now I want a subtotal of goods of each city whereas goods send (PlaceFrom) = minus and goods received (PlaceTo) = plus.

So I want a 2nd table with this result:

Place	GoodsTotal
A	10
B	-10
C	-20
D	50
E	-30

How do I get this second table?

Re: Sum based on different columns

tresB — Wed, 08 Aug 2018 12:06:26 GMT

In the script or front-end chart?

Re: Sum based on different columns

sunny_talwar — Wed, 08 Aug 2018 12:14:51 GMT

May be create a link table like this

Table:

LOAD RowNo() as Key,

LOAD * INLINE [

PlaceFrom, PlaceTo, Goods

A, B, 20

C, D, 20

B, A, 30

E, D, 30

];

LinkTable:

LOAD Key,

PlaceFrom as Place,

'From' as Flag

Resident Table;

Concatenate (LinkTable)

LOAD Key,

PlaceTo as Place,

'To' as Flag

Resident Table;

and then...

Dimension

Place

Expression

=Sum({<Flag = {'From'}>}Goods) - Sum({<Flag = {'To'}>}Goods)

Re: Sum based on different columns

sunny_talwar — Wed, 08 Aug 2018 12:16:14 GMT

My bad, you might need

=Sum({<Flag = {'To'}>}Goods) - Sum({<Flag = {'From'}>}Goods)

Re: Sum based on different columns

jharke — Wed, 08 Aug 2018 12:55:10 GMT

At first I would liked it to have it in on the UI side. I mentioned 'goods' in my question, but actually it is about pallet packing and sometimes we get pallets in return at the deliveryside and there are some other issues that I have to take into consideration in an expression right now also.

But this 'sum based on different fields' was the hardest part for me. When it is in the script, it is 'secured safely' so I will try the suggestion of Sunny also.

Re: Sum based on different columns

jharke — Wed, 08 Aug 2018 14:13:17 GMT

Works great! Fantastic!

Something like this was on my mind, but now I have a 'script to express'.

-happy-