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: 
slribeiro
Partner - Creator
Partner - Creator

Aggregatte similar rows

Greetings. I'm interested in aggregate some lines of a table in frontend.

Imagine that i have the following situation:

Field1                    | Area

Building A1            | 100

Building A2            | 110

Building A3            | 120

Building A4            | 130

Building B1            | 105

Building B2            | 115

Building C1            | 80

Building C1            | 150

and i want to have a final result like:

Field1                  | Area

Building A            | 460

Building B            | 220

Building C            | 230


Thanks in advance, hope you can help me.

1 Solution

Accepted Solutions
pokassov
Specialist
Specialist

Hi!

dimention:

PurgeChar(Field1,'0123456789')    

View solution in original post

5 Replies
sebastianlettner
Partner - Creator
Partner - Creator

Hi,

you can usa a calculated dimension. In this case it would be Left(Field1, Len(Field1) - 1) and Sum(Area) as expression.

Regards

Sebastian Lettner

pokassov
Specialist
Specialist

Hi!

dimention:

PurgeChar(Field1,'0123456789')    

slribeiro
Partner - Creator
Partner - Creator
Author

that is a very particular case. What if the words doesn't have the same size... Like, House A1, Apartment A2, Villa A3, and so on... How do i put all these fields as Buildings A for example?

sebastianlettner
Partner - Creator
Partner - Creator

Hi,

that might be a bit difficult. But it looks like you have pattern like [WordWithDiffenentLength][BLANK][GROUPwithFIXLength][SubGroup]. If so you can use

Left(Field1, Index(Field1, ' ') + [LengthOfGroup])

e.g.

Left(Field1, Index(Field1, ' ') + 1)      1 for A,B,C

Regards

Sebastian Lettner

sebastianlettner
Partner - Creator
Partner - Creator

Hi,

just a hint, you should use that logic to create an additional field in your data moddel, because calculated dimensions are quite heavy to calculate.

Just add

...

Left(Field1, Index(Field1, ' ') + 1)  as AggreagateGroup

...

to your table and use AggreagateGroup as Dimension to improve the chart calcualtion performance

Regards

Sebastian Lettner