Clasify numbers in a column into a range

[oscargm_bi]

Hi all,

I need help of you guys... I have a column with many numbers, i need to clasify them in some range in a new column, like 1 to 100, 101 to 300, 300 to 500, 501 to 1000. How can i do that in the script?

Thanks

[johngouws · Accepted solution]

Hi. Try something like this: 

t1:
LOAD
*,
if(Number <= 100, '0 to 100',
if(Number > 100 and Number <= 200 , '101 to 200',
if(Number > 200 and Number <= 300 , '201 to 300',
if(Number > 300 and Number <= 400 , '301 to 400',
if(Number > 400 and Number <= 500 , '401 to 500',
if(Number > 500 and Number <= 600 , '501 to 600',
if(Number > 600 , '> 600'
))))))) as [Number Group]
;
LOAD * INLINE [
Number
21
35
101
100
150
10
180
220
280
302
399
400
401
450
499
500
550
560
600
601
];
exit script;

It will produce this: Sort the 'Number Group' by Load order: 

[Vegar]

You can also use IntervalMatch to solve this.

Transactions:

Load 

[The Number column],

[Other columns]

From [Your Source];

[Class table]:

Load * inline [

A, B, Class

1,100, '1-100'

101, 300, '101-300'

301, 500, '301-500'

501, 1000, '501-1000'

1001, 10000000, '>1001'

];

Intervalmatch :

Intervalmatch ([The Number column])

Load A, B

Resident [Class table];

[Channa]

if the range is constant you can use Class() function

[oscargm_bi]

Thank you, this solution was exactly what I wanted.

Regards.