Relationship between two tables (many to one value)

[#1]

Hello everybody,

i have the following question. I have a table witch contains Employee data like Number, Name and Function.

Also i have a Table that contains the Function characteristic. Here are the example:

FunctionTable:

function_nr	function
1	Employee
2	management
3	trainer
4	trainee

Employee Table:

emp_nr	function_nr	lastname	name
1	1##4	Smith	John
2	1##3##2	Colle	Tim
3	1##	Meier	Tina

Now my question is, how i can link them together so that every function number in the Employee Table would pick all the functions?

Thank you for help.

Have a nice day.

[lironbaram · Accepted solution]

you can do this by separating the employee table to two tables

your script will look something like

Employee:

load emp_nr, lastname ,nane

from EmployeeTable ;

Employee_function:

load emp_nr,

       subfield(function_nr,'##') as function_nr

From EmployeeTable;

FunctionTable:

load * From functionTable;

[Peter_Cammaert]

Like this:

LinkTable:

LOAD emp_nr, subfield(function_nr, '##') AS function_nr

RESIDENT Employee_Table;

DROP Field function_nr FROM  Employee_Table;

Add this to your script after loading Employee_Table.

The two-parameter version of SubField() will create a link record for every value in function_nr.

[Colin-Albert]

You can use a mapping load to get the function name and then use subfield to appy the maping to the Employees table.

Function_Map:

mapping load

     function_nr,

    function

from [function data table]

;

Employees:

load

     emp_nr,

      applymap('Function_Map', subfield(function_nr, '##')) as function,

     lastname,

     name

from [employee data table]

[kiranmanoharrode]

Hi Tigetlily,

Use subfield() function  for Employee Table,

Employee:

Load emp_nr,

         Subfield(function_nr,'##')  as function_nr,

         lastname,

         name

From employee.qvd;

You will get below output for above script;

emp_nr	function_nr	lastname	name
1	1	Smith	John
1	4	Smith	John
2	1	Colle	Tim
2	3	Colle	Tim
2	2	Colle	Tim
3	1	Meier	Tine

[#6]

Hello,

thank you all for the answer. Now it works and i'm so happy 😉