topic Re: Add data from field to another field in QlikView

Add data from field to another field

Wed, 10 Feb 2016 19:41:37 GMT

I have a table like below that I would like to create a new field with manager names. The ID for every manager is in field Manager_ID and all employess names is in the filed Full_Emp_Name. All employess ID is in field Employee_ID, also the managers.

Employee:

LOAD Emp_Lastname,

Emp_Firstname,

Employee_ID,

Full_Emp_Name,

Manager_ID,

TrafficArea,

Unit

FROM

(qvd);

I hope you understad what I'm looking for and have any suggestion.

Re: Add data from field to another field

sunny_talwar — Wed, 10 Feb 2016 20:03:30 GMT

May be try this:

Employee:

LOAD Emp_Lastname,

Emp_Firstname,

Employee_ID,

Full_Emp_Name,

Manager_ID,

TrafficArea,

Unit

FROM

(qvd);

Join (Employee)

LOAD Emp_Lastname as Manager_Lastname,

Emp_Firstname as Manager_Firstname,

Manager_ID as Employee_ID

Resident Employee;

Re: Add data from field to another field

maxgro — Wed, 10 Feb 2016 20:05:48 GMT

maybe

MapManager:

Mapping load

Employee_ID,

Full_Emp_Name

FROM (qvd);

Employee:

LOAD

Emp_Lastname,

Emp_Firstname,

Employee_ID,

Full_Emp_Name,

Manager_ID,

TrafficArea,

Unit,

ApplyMap('MapManager', Manager_ID) as Full_Mgr_Name

FROM (qvd);

Re: Add data from field to another field

harsh44_bhatia — Wed, 10 Feb 2016 20:09:09 GMT

You need to do a self join with the employee logical table. We do this when we have to derive fields from within the same table.

do something as follows:

Employee:

LOAD Emp_Lastname,

Emp_Firstname,

Employee_ID,

Full_Emp_Name,

Manager_ID,

TrafficArea,

Unit

FROM

(qvd);

left join(Employee)

load

Manager_ID as Employee_ID,

Emp_Firstname & ' ' & Emp_Lastname as ManagerName

resident

Employee;

Re: Add data from field to another field

MK_QSL — Wed, 10 Feb 2016 20:12:17 GMT

Employee:

LOAD Emp_Lastname,

Emp_Firstname,

Employee_ID,

Full_Emp_Name,

Manager_ID,

TrafficArea,

Unit

FROM

(qvd);

temp:

Load Distinct Manager_ID Resident Data;

Left Join (temp) Load Employee_ID as Manager_ID, Full_Emp_Name as Manager_Name Resident Data;

Left Join (Data) Load * Resident temp;

Drop Table temp;

Re: Add data from field to another field

Peter_Cammaert — Wed, 10 Feb 2016 21:29:34 GMT

IMHO Employee tables usually already are distinct. If so, this simple version will do the same job:

Employee:

LOAD Emp_Lastname,

Emp_Firstname,

Employee_ID,

Full_Emp_Name,

Manager_ID,

TrafficArea,

Unit

FROM (qvd);

LEFT JOIN (Employee)

LOAD Employee_ID AS Manager_ID, // JOIN on Manager ID, not Employee ID !!!

Full_Emp_Name AS Manager_Full_Name

RESIDENT Employee;

Best,

Peter