Sum of each digit of number

[Sagar_Apte]

Hi Friends,

I want to calculate the sum of each digit of the given number. I have a field in data which stores the numbers. Below is the requirement

Number
12345
1234
123

Output need
Number      Sum
12345           15
1234             10
123                6

I know it should be done using for loop but not able to get it done. Also note that Number is not fixed length

[Vegar]

Try something like this. 

Tmp:

Load recno() as Id, Number, mid(Number, Iterno() ) as CrossNumber inline [

Number

12345

1234

123]

While iterno() <= len(Number) ;

Final:

Load ID, Number, sum(CrossNumber) as CrossSum

Resident Tmp

Group by ID, Number;

Drop table Tmp;

[Kushal_Chawda]

another approach

Data:
Load * Inline [
Number
12345
123
1234];

let vSum = null();

for Each i in FieldValueList('Number')

for j=1 to Len('$(i)')

let vSum = rangesum('$(vSum)',mid('$(i)','$(j)',1));

Next j

Final:
Load '$(i)' as Number,
     '$(vSum)' as Sum
AutoGenerate 1;

let vSum = Null();

next i

[Saravanan_Desingh]

One solution is.

tab1:
LOAD * INLINE [
    Number
    12345
    1234
    123
];

Left Join(tab1)
LOAD Number, Sum(Char) As SumNum
Group By Number;
LOAD Number, Mid(Number,IterNo(),1) As Char
Resident tab1
While IterNo()<=Len(Number)
;

[Saravanan_Desingh]

Output.

[Sagar_Apte]

Hi All,

Thanks for your reply but  I am working on huge data set and doing iterno() and group by causing lot of time and also consuming memory, Is there any other way we can do it?

[Kushal_Chawda · Accepted solution]

May be you can try something like below. This approach will be definitely faster than the iterno() . 

// Assuming this is your table. Replace it with your input data
Data:
LOAD 
    Number
FROM Data.qvd;

// Get max number from Number field. This is fastest way to calculate Max
MaxNum:
Load Max(Number) as MaxNum;
Load FieldValue('Number',RecNo()) as Number
AutoGenerate FieldValueCount('Number');

let vMaxNumberLen = len(Peek('MaxNum',0,'MaxNum'));

let vFieldName ='Number';  // Field name which stores the Numbers.

// Generate the rangesum formula on Max number lenght
Let vFormula1 = 'Rangesum(';

for i=1 to '$(vMaxNumberLen)'

let vFormula1 = '$(vFormula1)'& 'mid($(vFieldName),'&$(i)&',1),';

Next

let vFormula = mid('$(vFormula1)',1,len('$(vFormula1)')-1)&')';

// Use vFormula variable as logic to get the sum of all digit number
Final:
Load *,
     $(vFormula) as Sum  
Resident Data;

Drop Table Data;

[Sagar_Apte]

@Kushal_Chawda  Thanks for your input. I will try and get back to you

[Sagar_Apte]

@Kushal_Chawda  - Your solution works fine and really like the way you interpreted. Thanks a lot.

[Kushal_Chawda]

I am glad that it worked.