Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Partner
Partner

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

Re: Employee Lookup Table

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;

3 Replies
andrey_krylov
Valued Contributor

Re: Employee Lookup Table

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

Luminary
Luminary

Re: Employee Lookup Table

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
Valued Contributor

Re: Employee Lookup Table

You can use Interval Match to assign category to Employee.

PFA solution.