Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Nino
Contributor III
Contributor III

Classifying in variable ranges

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.

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

2 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Nino
Contributor III
Contributor III
Author

Such a nice solution!

Thank you so much.

N.