Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
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);
That worked! Exactly what i was looking for.
Thanks so much :0)
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)