Classifying in variable ranges

[Nino]

Hello guys,

I need to classify the employees basing on their seniority; the ranges aren't all equals each others; the 1st one is beween 1 and 2 years, the 2nd one is from 3 up to 5, the 3rd is 6-10, the 4th is 11-20, the 5th over 20.

Can you suggest me a simple way to create these ranges and explain me how to put the count(EmployId) in the right range?

Thank you so much.

N.

[jonathandienst · Accepted solution]

You can use a nested-if (simple to build but hardcoded and hard to maintain), or through interval (data driven). 

Nested If

If(Seniority <= 2, Dual('<2', 1),
If(Seniority <= 5, Dual('3-5'), 3,
If(Seniority <= 10, Dual('6-10'), 6,
...
))) as SeniorityGroup,

Interval match

Intervals:	
LOAD From, To, Dual(Label, RowNo()) as Group
Inline
[
	From, To, Label
	1, 2, <2
	3, 5, 3-5
	6, 10, 6-10
	...
];

Left Join (Data)
IntervalMatch(Seniority)
LOAD From, To
Residen Intervals;

// Optional cleanup
Left Join (Data)
LOAD From, To, Group
Residen Intervals;

DROP Table Intervals;
DROP Fields From, To;

The latter script assumes that your data table containing the seniority is called Data, and that the seniority is integer values.

[Nino]

Such a nice solution!

Thank you so much.

N.