topic Re: Employee Lookup Table in QlikView

Employee Lookup Table

azmeerrehan — Fri, 02 Nov 2018 15:16:54 GMT

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

Re: Employee Lookup Table

andrey_krylov — Fri, 02 Nov 2018 18:08:00 GMT

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

Re: Employee Lookup Table

juraj_misina — Fri, 02 Nov 2018 23:23:40 GMT

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,

Re: Employee Lookup Table

passionate — Sat, 03 Nov 2018 05:09:32 GMT

You can use Interval Match to assign category to Employee.

PFA solution.