Extract number from even position of string and sum it

[rob_vander]

Hi All,

I have weird requirement. I have below data. I want to extract number from even position (2nd,4th... ) of the String and sum those numbers to create Value field. I need help to create Value field in the script. My preference is script solution but I am fine with front ens solution if it is not complex.

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

[rwunderlich · Accepted solution]

How about:

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
]
;

Values:
LOAD 
  ID,
  Sum(char) as Value
Where Even(Position)
Group By ID
;
LOAD
ID,
  IterNo() as Position,
  Mid(String,IterNo(), 1) as char
Resident Data
While IterNo() <= len(String)
; 

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Kushal_Chawda · Accepted solution]

@rob_vander  If you know maximum length of String which cannot exceed, you could follow below approach which is performant if your dataset is large. 

// Generate even numbers for maximum length string

let vMaxEvenNumber = 20;

for i=1 to '$(vMaxEvenNumber)'

even_number:
Load $(i) as even_number
AutoGenerate 1
where even($(i))=-1;

Next

// Generate rangesum formula for each even number position of the string

let vFieldName = 'String'; // field name of string

formula:
Load 'rangesum(' & Concat(formula,','& Chr(10),even_number) & ')' as formula;
Load even_number,'mid($(vFieldName),'& even_number & ', 1)' as formula
Resident even_number;

Drop Table even_number;

let vFormula = Peek('formula');

Drop Table formula;

Data:
Load ID, 
     $(vFormula) as Value
FROM Table;

[rob_vander]

Thanks it works

[rob_vander]

This is something different way but my dataset is not huge so I have used other script suggestion. But it also works as we have fixed length String.