topic Re: Extract number from even position of string and sum it in App Development

Extract number from even position of string and sum it

rob_vander — Mon, 12 Aug 2024 15:59:54 GMT

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

Re: Extract number from even position of string and sum it

rwunderlich — Mon, 12 Aug 2024 23:30:03 GMT

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

Re: Extract number from even position of string and sum it

Kushal_Chawda — Tue, 13 Aug 2024 09:50:50 GMT

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

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

Re: Extract number from even position of string and sum it

rob_vander — Tue, 13 Aug 2024 15:33:39 GMT

Thanks it works

Re: Extract number from even position of string and sum it

rob_vander — Tue, 13 Aug 2024 15:35:32 GMT

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.