topic How to split string and add the numbers in QlikView

How to split string and add the numbers

hervechambrin — Tue, 22 Dec 2015 15:54:08 GMT

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.

Re: How to split string and add the numbers

sunny_talwar — Tue, 22 Dec 2015 17:11:47 GMT

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

Re: How to split string and add the numbers

hervechambrin — Tue, 22 Dec 2015 17:53:17 GMT

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

Re: How to split string and add the numbers

sunny_talwar — Tue, 22 Dec 2015 18:04:32 GMT

May be this:

Table:

LOAD Sum(subs) as subs;

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

LOAD * Inline [

Field

1;170;15;100

];

Re: How to split string and add the numbers

effinty2112 — Wed, 23 Dec 2015 10:34:40 GMT

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

Re: How to split string and add the numbers

hervechambrin — Wed, 23 Dec 2015 14:06:28 GMT

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
];

Re: How to split string and add the numbers

sunny_talwar — Wed, 23 Dec 2015 15:59:23 GMT

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

];

Re: How to split string and add the numbers

hervechambrin — Wed, 23 Dec 2015 16:13:29 GMT

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.

Re: How to split string and add the numbers

effinty2112 — Wed, 23 Dec 2015 16:44:33 GMT

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

Re: How to split string and add the numbers

hervechambrin — Wed, 23 Dec 2015 17:24:47 GMT

Thank you All, this is working for me.

Re: How to split string and add the numbers

settu_periasamy — Wed, 23 Dec 2015 17:27:25 GMT

Try this also, if suits..

Table:
LOAD AutoNumber(Field) as Rec,SubField(Field, ';') as subs;
LOAD * Inline [
Field
1;170;15;100
1;10;15;110
];

NoConcatenate
T2:
LOAD Sum(subs) as subs,Rec Resident Table Group by Rec;

DROP Field Rec;
DROP Table Table;