topic Re: How do I group my data? in QlikView

How do I group my data?

samvile18 — Thu, 20 Jun 2013 15:07:39 GMT

Hi all,

Quick question...how do I go about grouping some of my underlying data??

This is what I currently have:

A field named PRTY with data like this 100, 101, 102, 200, 201, 203, 300, 308, 309, 400 etc

How do I get that field to map to a high level group, so instead of showing the above it groups into fields like:

000+

100+

200+

300+

400+

So the 100's would go into 100+, the 200's would go into 200+ and so on...

Any help is greatly appreciated

Re: How do I group my data?

Anonymous — Thu, 20 Jun 2013 15:23:36 GMT

It's ugly but in a dimension you could do =Round(if(PRTY=0,PRTY,PRTY-50),100)

Re: How do I group my data?

Gysbert_Wassenaar — Thu, 20 Jun 2013 15:28:21 GMT

You can use the floor function: floor(PRTY,100) as PRTY_Group. If you want the + added so you get 100+ I'd use this: dual(floor(PRTY,100)&'+',floor(PRTY,100)) as PRTY_Group. That way it will display as 100+ etc, but can still be sorted numerically.

Re: How do I group my data?

calvindk — Thu, 20 Jun 2013 15:31:19 GMT

In script:

repeat('0', (3 - len(Floor(PRTY/100)))) & Floor(PRTY/100) & '+' as GroupName

or in expression:

repeat('0', (3 - len(Floor(PRTY/100)))) & Floor(PRTY/100) & '+'

Re: How do I group my data?

samvile18 — Fri, 21 Jun 2013 09:32:28 GMT

This is brilliant, thanks for all of the help.