Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

remove employee id value appended to name

Our source database has our employee name & ID in the same field, for example "John Smith 123456"

I want to remove the id part and just show "John Smith" etc

Obviously the character length of each name changes but they are always separated by a space, so I'd need to remove the characters after the second space!

I'd like to do this in the edit script, and created as a new filed in the load, so the original version is still available

What's best way??

Thanks

1 Solution

Accepted Solutions
Peter_Cammaert
Partner - Champion III
Partner - Champion III

If the IDs are strictly numerical (only digits) and your employee names do not contain any digits, your can use something like

... trim(PurgeChar([Employee Field], '0123456789')) AS EmployeeName, ...

Peter

View solution in original post

4 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

If the IDs are strictly numerical (only digits) and your employee names do not contain any digits, your can use something like

... trim(PurgeChar([Employee Field], '0123456789')) AS EmployeeName, ...

Peter

fvelascog72
Partner - Specialist
Partner - Specialist

Hi,

If ID length is always the same (n), you can use right(FIELD,n) as ID

and left(FIELD,(len(FIELD)-(n+1)) as Employee

Not applicable
Author

Perfect thank you Peter

Not applicable
Author

if you want to base your derivation on a space you can try this:

mid([Employee Field],index([Employee Field],' '))

purgeChar might cause an issue if the id is alpha numeric. if it is not the case then purgechar is good to go.

Also if you decide you want the ID as a column and it is always numeric, you could use the keep char.

KeepChar([Employee Field], '0123456789') AS EmployeeID