Split numbers and add them

[rakesh_kumar]

Hi Guys,

I need help with splitting numbers in a field in Script and adding them like this:

For example, 1234     -->     1+2+3+4 = 10

This is a Table with more than 100K rows, and values with variable lengths,  length of the longest value is 31 characters, so need help with doing it in the Script.

Appreciate your help.

Thanks

[justISO]

Hi, here you can find more than 1 approach to achieve same goal:

https://community.qlik.com/t5/New-to-Qlik-Sense/Sum-of-each-digit-of-number/td-p/1724665

[tresesco]

You can also try like below; with this you wouldn't have to iterate over values to split them:

RangeSum($(=Mid(
Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(ValueField, '1',',1'),'2', ',2'), '3',',3'),'4',',4'),'5',',5'),'6',',6'),'7',',7'),'8',',8'),'9',',9'),'0',',0')
,2,100)))

[marcus_sommer]

In addition to the suggestion of @tresesco the $(= ...) kind of $-sign expansion isn't available within the script but you could replace it with evaluate(). Further instead of the nested replace-functions you may apply a mapping with mapsubstring() ... I would be interested if there is a significantly difference between the replace-nesting and a mapping-approach.

- Marcus

[MarcoWedel · Accepted solution]

one example using this method to insert plus signs  and evaluate the resulting addition:

mapDigits:
Mapping
LOAD RecNo()-1,
     '+'&(RecNo()-1)
AutoGenerate 10;

tabNumbers:
LOAD Text(Number) as Number,
     Evaluate(MapSubString('mapDigits',Number)) as DigitSum
Inline [
Number
2
9
566
630
698
2386
5180
8127
40160
412751
416791
28905452
55055522
1795249199
2066002667
3158794532
9751984102
31699768581
55466099034
5846177080021
31887836673808
44327811117597
54119976334149
91137840807090
145973497204024
268752297142053
784549094256608
12981211113774523
20760317595731232
638008778871628819558
643614070737401461371
929029358173468305629
7807745508504144666070
34749834263856782068353
39362066826967438201398
828472354748652381344956
2672834582427231997662512
5980451253402669281264489
6980126542802998219773776
74701116659901724378466386
86469079237551047052117454
289283232708003202322032529
316010862579655378192390551
539065515218719696522406496
550326747194356984468667150
699037397796948463076154987
1090261718243966472389526485
6297834313789734045443952384
16526525975103780351358596724
960620364526238294701514260843
];

Gruß

Marco

[rakesh_kumar]

Hi Treseco,

Thanks for the reply, I tried, but it's giving insufficient parameters in Rangesum.

Would you mind sharing an example QlikView file with your solution with some random data?

Appreciate your help!

Thanks

[rakesh_kumar]

Thanks Marco, your solution worked like a charm 👍

[MarcoWedel]

thanks, but actually it was not mine as Marcus first came up with the idea.

[rakesh_kumar]

Thanks @tresesco, @marcus_sommer and @MarcoWedel for helping with the solution 🤝