topic Re: Relationship between two tables (many to one value) in QlikView

Relationship between two tables (many to one value)

Mon, 12 Jan 2015 08:25:41 GMT

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.

Re: Relationship between two tables (many to one value)

lironbaram — Mon, 12 Jan 2015 08:32:29 GMT

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;

Re: Relationship between two tables (many to one value)

Peter_Cammaert — Mon, 12 Jan 2015 08:34:47 GMT

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.

Re: Relationship between two tables (many to one value)

Colin-Albert — Mon, 12 Jan 2015 08:40:08 GMT

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]

Re: Relationship between two tables (many to one value)

kiranmanoharrode — Mon, 12 Jan 2015 08:40:15 GMT

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

Re: Relationship between two tables (many to one value)

Mon, 12 Jan 2015 09:05:55 GMT

Hello,

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