Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
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)
;
Output.
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?
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;
@Kushal_Chawda Thanks for your input. I will try and get back to you
@Kushal_Chawda - Your solution works fine and really like the way you interpreted. Thanks a lot.
I am glad that it worked.