Extract even position from string and sum it

[rob_vander]

Hi All

I have weird requirement. I have below data. I want to extract numbers from even position 2nd, 4th... and sum those number to create value field. I need a help to create Value field in the script. My preference is script but front end solution should also be fine if not complex

ID	String	Value
1	6275390705	2+5+9+7+5=28
2	6349304926	3+9+0+9+6=27
3	3770493940	and so on
4	3501712988
5	2119488328
6	879038653
7	5498974641
8	7644528791
9	6174749785
10	8965006299

[Sayed_Mannan]

Hi, here is the solution,

Data:
LOAD * INLINE [
ID, String
1, 6275390705
2, 6349304926
3, 3770493940
4, 3501712988
5, 2119488328
6, 879038653
7, 5498974641
8, 7644528791
9, 6174749785
10, 8965006299
];

// Function to Extract Even Position Digits and Sum

DataWithValues:
LOAD
ID,
String,
RangeSum(
Mid(String, IterNo() * 2, 1)
) AS Value
Resident Data WHILE IterNo() <= Len(String); 

Drop Table Data;

exit script;

in the table use measure SUM(Value).

I hope it helps, if this resolve your query then please mark this as solution.

Cheers,

Sayed Mannan Ahmad

[Or · Accepted solution]

@Sayed_Mannan has laid out the basics, but that's not quite ideal. Expanding on the original,

Data:
LOAD * INLINE [
ID, String
1, 6275390705
2, 6349304926
3, 3770493940
4, 3501712988
5, 2119488328
6, 879038653
7, 5498974641
8, 7644528791
9, 6174749785
10, 8965006299
];

// Extract Even Position Digits

DataWithValues:
LOAD
ID,
Mid(String, IterNo() * 2, 1) AS Value
Resident Data WHILE IterNo()*2 <= Len(String); // Previously we had twice as many iterations as required

// Sum and connect to the original table

Left join (Data)
Load ID, Sum(Value) resident DataWithValues Group By ID;

Drop Table DataWithValues;

There's probably a cleaner approach but this one should work nicely.