Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Please Can you add a sample file ?
I could not.
thanks.
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.
Please help me ...