Employee Lookup Table

[azmeerrehan]

I have an Employee_Data with Employee's IDs and their Salarys. Then I have a lookup table for the range of the salaries. Based on the Range the Emp are categorized into different Categories for example  salary Range between 100k and 115k is cat 1 and vice versa. I would like to add the Category column to each Empl ID based on their Salary.

How I can accomplish that in the script

[andrey_krylov]

Hi Rehan. I did not find a more elegant way. However...

LOAD * Inline [
Range,		Category
10000,		1
115000,		2
120000,		3
130000,

];

and dimension is

=Aggr(Max(If(Aggr(Sum({1} Amount), Employee, Category) > Range, Category)), Employee)

see attached file

[juraj_misina · Accepted solution]

Hi,

you need IntervalMatch feature for this.

EmpData:

LOAD

EmpID,

Salary

From Employee_Data;

Left Join(EmpData)

IntervalMatch(Salary)

LOAD

SubField(Range, '-', 1) as [From],

SubField(Range, '-', 2) as To

From Lookup_Table;

Left Join(EmpData)

LOAD

SubField(Range, '-', 1) as [From],

SubField(Range, '-', 2) as To,

Category

From Lookup_Table;

Drop Fields [From], To;

[passionate]

You can use Interval Match to assign category to Employee.

PFA solution.