Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
BrunPierre
Partner - Master II
Partner - Master II

summing numbers in a multivalued field

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>
Labels (1)
1 Solution

Accepted Solutions
MayilVahanan

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

7 Replies
edwin
Master II
Master II

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:

commQV28.PNG

BrunPierre
Partner - Master II
Partner - Master II
Author

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

Thank you!

BrunPierre
Partner - Master II
Partner - Master II
Author

@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

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

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
BrunPierre
Partner - Master II
Partner - Master II
Author

That's fantastic @MayilVahanan, It works perfectly. 

Cheers!