Sum based on different columns

[jharke]

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?

[tresesco]

In the script or front-end chart?

[sunny_talwar · Accepted solution]

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)

[sunny_talwar]

My bad, you might need

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

[jharke]

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.

[jharke]

Works great! Fantastic!

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

-happy-