Solved: How to split string and add the numbers - Qlik Community

hervechambrin · ‎2015-12-22

Hi All ,

I have a field with a string like 'Y1C170B15F100', a letter followed by a number between 1 and 999 and repeat up to 20 times.

I need to parse it to load a sum of the integers, in that case above the result would be '286'.

Thank you for your help.

effinty2112 · ‎2015-12-23

Hi Herves,

tews:

Load Capacity,

Evaluate(

Replace(

Capacity,

'A','+'),

'B','+'),

'C','+'),

'D','+'),

'E','+'),

'F','+'),

'G','+'),

'H','+'),

'I','+'),

'J','+'),

'K','+'),

'L','+'),

'M','+'),

'N','+'),

'O','+'),

'P','+'),

'Q','+'),

'R','+'),

'S','+'),

'T','+'),

'U','+'),

'V','+'),

'W','+'),

'X','+'),

'Y','+'),

'Z','+')

)

as tews

;

LOAD * INLINE [

Capacity

A10B20

];

Gives a table

Capacity	tews
A10B20	30

View solution in original post

sunny_talwar · ‎2015-12-22

Not sure how you derived 286 from Y1C170B15F100. Would you be able to walk us through the logic?

hervechambrin · ‎2015-12-22

Thank you for taking the time. I found a way to get the string as '1;170;15;100' , if you add theses numbers it will be 286. but now when I try to sum theses number I need theses 2 steps :

sub2:
LOAD
SubField('1;170;15;100',';') as sub2s
resident Capacity;

add:
LOAD
sum(sub2s) as addedfinal
resident sub2;

The following combination of sum and subfield is not working, any idea why ?

sub:
LOAD
sum(SubField('1;170;15;100',';')) as subs
resident Capacity;

Thanks

sunny_talwar · ‎2015-12-22

May be this:

Table:

LOAD Sum(subs) as subs;

LOAD SubField(Field, ';') as subs;

LOAD * Inline [

Field

1;170;15;100

];

effinty2112 · ‎2015-12-23

Hi Herve,

This one is great fun! Try this expression

=

num(

$(=

Replace(

'$(vString)',

'A','+'),

'B','+'),

'C','+'),

'D','+'),

'E','+'),

'F','+'),

'G','+'),

'H','+'),

'I','+'),

'J','+'),

'K','+'),

'L','+'),

'M','+'),

'N','+'),

'O','+'),

'P','+'),

'Q','+'),

'R','+'),

'S','+'),

'T','+'),

'U','+'),

'V','+'),

'W','+'),

'X','+'),

'Y','+'),

'Z','+')))

This will return the number you're looking for for whatever string you set vString equal to.

vString	Y1C170B15F100
vSum	286

hervechambrin · ‎2015-12-23

Thank you Andrew, is it possible to get the same result in a LOAD script, I tried a lot of different syntax but always get the same result ? The following returns '+10+20'

tew:
LOAD
num#(Replace(Replace(Capacity,'A','+'),'B','+')) as tews
INLINE [
Capacity
A10B20
];

sunny_talwar · ‎2015-12-23

You were able to change letters into semi-colons already right? Have you tried this?

Table:

LOAD Sum(subs) as subs;

LOAD SubField(Field, ';') as subs;

LOAD * Inline [

Field

1;170;15;100

];

hervechambrin · ‎2015-12-23

Hello Sunny, I tried but it didn't work in my case I need to do it within 1 LOAD statement while looping thru multiples QVDs.

effinty2112 · ‎2015-12-23

Hi Herves,

tews:

Load Capacity,

Evaluate(

Replace(

Capacity,

'A','+'),

'B','+'),

'C','+'),

'D','+'),

'E','+'),

'F','+'),

'G','+'),

'H','+'),

'I','+'),

'J','+'),

'K','+'),

'L','+'),

'M','+'),

'N','+'),

'O','+'),

'P','+'),

'Q','+'),

'R','+'),

'S','+'),

'T','+'),

'U','+'),

'V','+'),

'W','+'),

'X','+'),

'Y','+'),

'Z','+')

)

as tews

;

LOAD * INLINE [

Capacity

A10B20

];

Gives a table

Capacity	tews
A10B20	30

hervechambrin · ‎2015-12-23

Thank you All, this is working for me.