Replacing employee ID in the chart with his name, from same table

[draginko]

Hey guys,

Maybe a very silly question, but I am new to Qlik and to scripting, but..

Have a table Employee, with Names of employees, their IDs, hours worked,  reports etc.

I want to diplay the amount of employees reporting to other eployees. Eg to employee with EMP_ID of 7 is reporting 15 other employees. Pretty simple.

But the thing is, that in Treemap I am using is still displaying the EMP_ID  instead of the employee name, eg Frank. 

Is there any way, how to display the associated Name, instead of his ID ? The Reports To value is refering to IDs, so it is numeric and the Names are in string.  Both columns are in one table.

For getting the amount of reports is working [Reports To] in Dimension, and Count(distinct[Full Name]) in Measures. Now I want to display Full Name instead of IDs. 

Was trying for hours to search for forums and videos, tried using pick, match, replace, fieldvalue and many other functions but it seems there is non working for me. Or maybe I am doing mistakes in scripting logic.

Any idea is much appreciated

[thi_pham]

As my understanding, you created a treemap with:
- Dimension formular = EMP_ID . Title = 'Reports to'
- Measure formular = Count(distinct[EMP_ID]).
In principle, you can just replace EMP_ID in both of your dimension and measure with the [FULL NAME] field and get the chart with [FULL NAME] displayed:
- Dimension formular = [FULL NAME]. Title = 'Reports to'
- Measure formular = Count(distinct[FULL NAME])
Be aware that if you have some employees with same [FULL NAME], you should have a solution to separate them.
It should work well. I think. Otherwise, can you share some dummy data for checking?

[draginko]

Thanks for your reply 🙂

However it is not working. With that syntax you sent me it was not working, still error in expression. I am using Qlik Sense Desktop. So far I have these related columns:

First Name, Last Name, Full Name, EMP_ID, Reports To (also many others, but they are not relevant for this chart).

In dimension:

=[Reports To]

In measures:

=Count([Reports To])

 If I replace anything, eg Reports To with Full Name, it is providing wrong outcome, usually always 1, because everybody has only one direct boss.

With these settings, it is displaying proper numbers, but instead of Names, there are EMP_IDs. And also I found out, that during using visualisations the chart is working in opposite way - instead of showing how many employees ARE reporting TO SELECTED employee, it is showing TO HOW MANY EMPLOYEES IS reporting the selected employee - so always one (and the EMP_ID of his boss, not name of his boss).

I am not allowed to send the data, but sending at least the outcome I am getting:

You can see, that instead of number 4 (top left) there should be name of the employee and the 7 should be how many accessaries he has.

Probably it should be something very simple I am missing, maybe some syntax or something...Basically I want to use one column in a table to be displayed by another (ID replaced by associated Name).

Thanks

[thi_pham]

Let try:
Dimension: [Reports to]
Measure: count(distinct EMP_ID)

[DavidŠtorek]

Hi,

to meet your requirements one option is to mapp new field to table called e.g. "ReportsTo.FullName" which will contain name of person with id corresponding with ReportsTo id. Than you can use this new field in your dimension instead of Reports To. I would also recomend to use count(ReportsTo) as measure...what if there will be two ore more employees with same name?

Regarding "issue" with using visualization...If you select name or id yo will filter out this record and what will be a result is to whom is given employee reporting. To find out how many employees are reporting to given person you have to select Reports To/"ReportsTo.FullName"

I attached an app where you can check what I suppose.

Hope that helps you 😉

[jmmayoral3]

Hello.

If your Employee_ID is a number, try to create a new field with DUAL(Emplyee_name, Employee_ID) .

This way you can show the name and operate with the numeric part of the field.