topic Re: Aggregatte similar rows in QlikView

Aggregatte similar rows

slribeiro — Thu, 22 Oct 2015 14:31:34 GMT

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.

Re: Aggregatte similar rows

sebastianlettner — Thu, 22 Oct 2015 15:28:38 GMT

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

Re: Aggregatte similar rows

pokassov — Thu, 22 Oct 2015 15:29:43 GMT

Hi!

dimention:

PurgeChar(Field1,'0123456789')

Re: Aggregatte similar rows

slribeiro — Thu, 22 Oct 2015 15:37:06 GMT

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?

Re: Aggregatte similar rows

sebastianlettner — Thu, 22 Oct 2015 15:54:49 GMT

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

Re: Aggregatte similar rows

sebastianlettner — Thu, 22 Oct 2015 16:00:55 GMT

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