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: 
Sagar_Apte
Contributor III
Contributor III

Sum of each digit of number

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

1 Solution

Accepted Solutions
Kushal_Chawda

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;

 

 

View solution in original post

9 Replies
Vegar
MVP
MVP

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.

commQV16.PNG

Sagar_Apte
Contributor III
Contributor III
Author

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

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
Contributor III
Contributor III
Author

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

Sagar_Apte
Contributor III
Contributor III
Author

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

Kushal_Chawda

I am glad that it worked.