Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
azmeerrehan
Partner - Creator
Partner - Creator

Employee Lookup Table

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

1 Solution

Accepted Solutions
juraj_misina
Luminary Alumni
Luminary Alumni

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;

View solution in original post

3 Replies
andrey_krylov
Specialist
Specialist

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
Luminary Alumni
Luminary Alumni

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
Specialist
Specialist

You can use Interval Match to assign category to Employee.

PFA solution.