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: 
leale1997
Contributor III
Contributor III

How to Group data by assigning Range of Content

Hi,

I have a column of data that contains %'s.  I would like to add a group to that column based on the content.  So if the content is 11% then the group = '10% to 20%'

This is what I have tried so far.  Warning i've been looking at this for so long i may be over complicating it.

if(IsNull([Act_Remaining_Shelf_life_Percent])<=0, '0%',
if([Act_Remaining_Shelf_life_Percent] >=0 and [Act_Remaining_Shelf_life_Percent]<=.10, '0% to 10%',
if([Act_Remaining_Shelf_life_Percent] >=.11 and [Act_Remaining_Shelf_life_Percent]<=.20, '10% to 20%',
if([Act_Remaining_Shelf_life_Percent] >=.21 and [Act_Remaining_Shelf_life_Percent]<=.30, '20% to 30%',
if([Act_Remaining_Shelf_life_Percent] >=.31 and [Act_Remaining_Shelf_life_Percent]<=.40, '30% to 40%',
if([Act_Remaining_Shelf_life_Percent] >=.40 and [Act_Remaining_Shelf_life_Percent]<=.49, '40% to 50%',
if([Act_Remaining_Shelf_life_Percent] >=.50 and [Act_Remaining_Shelf_life_Percent]<=.59, '50% to 60%',
if([Act_Remaining_Shelf_life_Percent] >=.60 and [Act_Remaining_Shelf_life_Percent]<=.69, '60% to 70%',
if([Act_Remaining_Shelf_life_Percent] >=.70 and [Act_Remaining_Shelf_life_Percent]<=.79, '70% to 80%',
if([Act_Remaining_Shelf_life_Percent] >=.80, '>80%',
))))))))))As Shelf_Life_Grp,  

I have attached the data I am sorting thru for reference.

Labels (2)
1 Solution

Accepted Solutions
Nicole-Smith

It is possible to write this shorter than what you currently have.  If you are okay with changing the format of the name of your buckets, you can write is shorter (like I have in [Shelf_Life_Grp]).  If you want the same format that you currently have, it's a little longer of an expression (like I have in [Shelf_Life_Grp2]).

Data:
LOAD [Record_ID], [Act_Remaining_Shelf_life_Percent],
IF([Act_Remaining_Shelf_life_Percent] <= 0, DUAL('0%', 0),
	IF([Act_Remaining_Shelf_life_Percent] >= .8, DUAL('>80%', .8),
		Class([Act_Remaining_Shelf_life_Percent], .1)
   )) AS [Shelf_Life_Grp],
IF([Act_Remaining_Shelf_life_Percent] <= 0, DUAL('0%', 0),
	IF([Act_Remaining_Shelf_life_Percent] >= .8, DUAL('>80%', .8),
		DUAL(Replace(Replace(Replace(Class([Act_Remaining_Shelf_life_Percent], .1), ' <= x <', '0% to '), '0.', '') & '0%', '00', '0'), Class([Act_Remaining_Shelf_life_Percent], .1))
   )) AS [Shelf_Life_Grp2];
LOAD
    Record_ID,
    Act_Remaining_Shelf_life_Percent
FROM [lib://Source Files/Group by % Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

screenshot.png

View solution in original post

4 Replies
Nicole-Smith

It is possible to write this shorter than what you currently have.  If you are okay with changing the format of the name of your buckets, you can write is shorter (like I have in [Shelf_Life_Grp]).  If you want the same format that you currently have, it's a little longer of an expression (like I have in [Shelf_Life_Grp2]).

Data:
LOAD [Record_ID], [Act_Remaining_Shelf_life_Percent],
IF([Act_Remaining_Shelf_life_Percent] <= 0, DUAL('0%', 0),
	IF([Act_Remaining_Shelf_life_Percent] >= .8, DUAL('>80%', .8),
		Class([Act_Remaining_Shelf_life_Percent], .1)
   )) AS [Shelf_Life_Grp],
IF([Act_Remaining_Shelf_life_Percent] <= 0, DUAL('0%', 0),
	IF([Act_Remaining_Shelf_life_Percent] >= .8, DUAL('>80%', .8),
		DUAL(Replace(Replace(Replace(Class([Act_Remaining_Shelf_life_Percent], .1), ' <= x <', '0% to '), '0.', '') & '0%', '00', '0'), Class([Act_Remaining_Shelf_life_Percent], .1))
   )) AS [Shelf_Life_Grp2];
LOAD
    Record_ID,
    Act_Remaining_Shelf_life_Percent
FROM [lib://Source Files/Group by % Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

screenshot.png

leale1997
Contributor III
Contributor III
Author

Thanks so much for responding. I am away from my computer right now but will be back at it shortly. This looks com promising. Can’t wait to try it.


leale1997
Contributor III
Contributor III
Author

That worked!  Exactly what i was looking for.  

Thanks so much :0)

leale1997
Contributor III
Contributor III
Author

Since you were such help with my last question.  Was wondering if you might have insight into one more.  

I would like to find the Min and Max value of a column based on the grouping it belongs to.  Grouping is designated by a combination of the values in two columns.

The data looks like this:

Category           Sub Category          Days

A                            1                                   10

A                            1                                    20

A                             1                                   30

So for Category A,1 the Min is 10 and the Max is 30

I attached a data sample for you.

Thanks in advance for any assistance :0)