split sum ?

[selcukcadir]

hi all,

Table 1

A1001

A1002

A1003

Table 2

row      amount          value

1               10                   A1001 , A1002

2               20                   A1002 , A1003

3               15                   A1003

4               12                   A1001

5               30                   A1002

Result Chart Table :

Value          Total Amount

A1001               22                       info :  (Row 1 + 4)

A1002               60                                  (Row 1 + 2 + 5)

A1003               35

How can I gather in this way?   there is a comma between the values

[Gysbert_Wassenaar]

When you load table 2 you can use the subfield function to create a record for every value in the comma separated list:

Table2:

Load row, amount, subfield(value, ',') as value

from ...table2source...;

Now you can use value as dimension and sum(amount) as expression in a table chart to get the totals you want.

[vivek_niti]

Hi,

First split the data in the field Value to different columns:

tab1:

LOAD

      row, amount,

      Subfield(value,',',1) AS Value1,

      Subfield(value,',',2) AS Value2

From.....

tab2:

LOAD row, amount, Value1 AS value

RESIDENT tab1;

CONCATENATE

LOAD row, amount, Value2 AS value

WHERE NOT ISNULL(Value2);

DROP TABLE tab1;

In front end just use the sum(amount) against value dimension..

Hope it helps....

Regards,

Vivek

[selcukcadir]

Please Can you add a sample file ?

I could not.

thanks.

[selcukcadir]

hi all,

Table 1

vaule               pb

A1001               eur

A1002              usd

A1003               eur

Table 2

row      amount          value

1               10                   A1001 , A1002

2               20                   A1002 , A1003

3               15                   A1003

4               12                   A1001

5               30                   A1002

Result Chart Table :

Value         pb     Total Amount

A1001        eur           22                       info :  (Row 1 + 4)

A1002        usd          60                                  (Row 1 + 2 + 5)

A1003        eur           35

How can I gather in this way?   there is a comma between the values

I forgot to add pb

plese thaks.

[selcukcadir]

Please help me ...