Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
suryaa30
Creator II
Creator II

Rangesum on single field with concatenated values

is Rangesum possible on single field with concatenated values?

Rangesum(Trimlist)

rangesum.JPG

Labels (1)
1 Solution

Accepted Solutions
suryaa30
Creator II
Creator II
Author

I tried belwo and it worked 😀Thanks Anyway for taking the time to respond.

T1:

LOAD * inline [

ID|Value

1|3

2|4

3|6

4|7

5|8

6|1

7|11

8|2

9|78

10|13

] (delimiter is '|');

 

T2:
NoConcatenate
Load *,
Rangesum(Peek(Sum),Value) AS Sum,
exp(Rangesum(log(Peek(Product)),Log(Value))) AS Product
RESIDENT T1
ORDER BY ID ASC;
DROP TABLE T1;

 

 

 

Exit script;

View solution in original post

5 Replies
marcus_sommer

The following worked with a manual string in a textbox - maybe you could adapt it to your field:

=rangesum($(=replace('1.1,2.2', chr(39), '')))

- Marcus

suryaa30
Creator II
Creator II
Author

Thanks, Could not get it, Below is the sample script that replicates my scenario? Coul d you please give it a try?

Even if it can be done on script level should work for me. Thanks again

rangesum.JPG

T1:

LOAD * inline [

ID|Value

1|3

2|4

3|6

4|7

5|8

6|1

7|11

8|2

9|78

10|13

] (delimiter is '|');

T2:


Load *,
Left(Right(List,Len(List)-1),Len(List)-2) as TrimList;

LOAD*,

if(1, Peek('List')&','&Value,Value) AS List

RESIDENT T1

ORDER BY ID ASC;
DROP TABLE T1;


Exit Script;

suryaa30
Creator II
Creator II
Author

T1:

LOAD * inline [

ID|Value

1|3

2|4

3|6

4|7

5|8

6|1

7|11

8|2

9|78

10|13

] (delimiter is '|');

T2:


Load *,

//Left(Right(List,Len(List)-1),Len(List)-2) as TrimList;

Mid(List,2) as TrimList;

LOAD*,

if(1, Peek('List')&','&Value,Value) AS List

RESIDENT T1

ORDER BY ID ASC;
DROP TABLE T1;


Exit Script;

marcus_sommer

My suggestion couldn't be applied within a chart on a dimensional level because the used $-sign expansion creates an adhoc-variable which is calculated globally before the chart is calculated and this result is taken for each row.

Of course a script-solution would be much better. This could be done with subfield(), for example with something like this:

load ID, subfield(TrimList, '|') as TrimListPart from Source;

- Marcus

suryaa30
Creator II
Creator II
Author

I tried belwo and it worked 😀Thanks Anyway for taking the time to respond.

T1:

LOAD * inline [

ID|Value

1|3

2|4

3|6

4|7

5|8

6|1

7|11

8|2

9|78

10|13

] (delimiter is '|');

 

T2:
NoConcatenate
Load *,
Rangesum(Peek(Sum),Value) AS Sum,
exp(Rangesum(log(Peek(Product)),Log(Value))) AS Product
RESIDENT T1
ORDER BY ID ASC;
DROP TABLE T1;

 

 

 

Exit script;