topic Re: Replace username to Last Name and First Name from different table in QlikView

Replace username to Last Name and First Name from different table

xarapre7 — Wed, 25 Nov 2020 16:16:04 GMT

Hi!

I have a table which has just a Username (ex. NTran) from Service table and would like to convert that to employee's full name. The first name and last name are in the Employee table. I want to know how can i replace the username to the employees' full name.

Thanks in advance.

Re: Replace username to Last Name and First Name from different table

swuehl — Wed, 27 Jul 2016 21:48:20 GMT

If your employee's table shows username next to the first name / last name, or if the username is built from first name / last name, you can use a mapping table approach:

Data Cleansing

Re: Replace username to Last Name and First Name from different table

xarapre7 — Wed, 27 Jul 2016 23:48:54 GMT

Thank you for that info.

I actually used Mapping as follows :

******************************************************

STName:
Mapping
LOAD Distinct

UserID as CreatedBy,
DisplayName

FROM
qvEmployee.qvd (qvd);

ServiceInfo:
LOAD

AutoNumber([Created By],'%CreatedBy') as %CreatedBy,

[STechnician]
, ApplyMap(‘STName’,[STechnician],'<Blank>') AS STechName

FROM
ServiceInfo.qvd (qvd)

tblEmployee:
LOAD
AutoNumber(UserID,'%CreatedBy') as %CreatedBy,

EmployeeID,
UserID,
DisplayName

FROM
Employee.qvd (qvd);

*********************************

but it's giving me multiple DisplayName values per STechnician

STechnician DisplayName

MNadale Robinson, Elie

MNadale Winda, Riza

MNadale Jayson, Lou

MNadale -

MNadale Pattison, Gary

Can you please help to correct the script?

Re: Replace username to Last Name and First Name from different table

Anil_Babu_Samineni — Thu, 28 Jul 2016 03:25:24 GMT

Just give mapping and use the sql where condition with append using the replace function

Re: Replace username to Last Name and First Name from different table

Thu, 28 Jul 2016 03:29:54 GMT

Use MappingLoad of the employee table and then use ApplyMap when loading your transaction table

Re: Replace username to Last Name and First Name from different table

Thu, 28 Jul 2016 03:32:52 GMT

See the sample here.

Re: Replace username to Last Name and First Name from different table

swuehl — Thu, 28 Jul 2016 06:27:38 GMT

Does field UserID in qvEmployee.qvd and field STechnician in ServiceInfo.qvd hold the same information (you are using these fields as key fields for your mapping)?

You should then look into the new field STechName to see the mapped values, not at DisplayName.