summing numbers in a multivalued field

[BrunPierre]

Hi guys, how do I always sum numbers in this multivalued field as depicted below i.e. only the red highlighted numbers?

<c167 m="62">1500.00</c167><c167 m="62" s="2">1500</c167><c167 m="62" s="3">1500</c167><c167 m="62" s="4">1500</c167><c167 m="62" s="5">1500</c167><c167 m="62" s="6">3000</c167><c167 m="62" s="7">3000</c167>

[edwin]

based on teh apparent pattern of your values you can use 

SubField(subfield(test,'</',n),'">',-1) 

where n is the nth value

[Saravanan_Desingh]

Try this,

tab1:
LOAD *, SubField(SubField(F1,'</',1),'>',2) As S1, IterNo() As ID
While IterNo()<=SubStringCount(F1,'</');
LOAD * INLINE [
    F1
    '<c167 m="62">1500.00</c167><c167 m="62" s="2">1500</c167><c167 m="62" s="3">1500</c167><c167 m="62" s="4">1500</c167><c167 m="62" s="5">1500</c167><c167 m="62" s="6">3000</c167><c167 m="62" s="7">3000</c167>'
];

[Saravanan_Desingh]

Output:

[BrunPierre]

@edwinAppreciate it very much, however, since the nth value may be limitless it been automated will be ideal.

Thank you!

[BrunPierre]

@Saravanan_Desingh  That is an insightful approach to automating the nth value from @edwin suggestion.

The output is unfortunately incorrect as the total should be 13,500.00. The 6th and 7th values are 3000.00 and not 1500.00.

Thank you a lot.

[MayilVahanan · Accepted solution]

Hi @BrunPierre 

Try like below

Load Base, Mid(SubField(Base, '</',IterNo()), Index(SubField(Base, '</',IterNo()),'">')+2) as Value While IterNo() <= SubStringCount(Base,'</');
LOAD * INLINE [
Base
'<c167 m="62">1500.00</c167><c167 m="62" s="2">1500</c167><c167 m="62" s="3">1500</c167><c167 m="62" s="4">1500</c167><c167 m="62" s="5">1500</c167><c167 m="62" s="6">3000</c167><c167 m="62" s="7">3000</c167>'
];

Edit:

In front end, sum(Value) gives 13500

[BrunPierre]

That's fantastic @MayilVahanan, It works perfectly. 

Cheers!