Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Sumnima
New Contributor

Duplicate Values in Merged Coulmn

//These are two columns of two tables that I have joined as a key. 

Table1: 

LOAD

.

trim(date#(if(substringCount(Release,'.')= 0,Replace(Replace(Right(Release, 5),'(',''),')','')))) & Resource as Key

Table2:

LOAD

trim(Month(Created))&[Assigned To] as Key

// "Person" is the made by merging "Resource " in Table1 and [Assigned To]  in Table2. 

tmpEmployeeTable:
Load distinct Resource as Person,Key
resident Rally;
Load distinct [Assigned To] as Person,Key
resident ServiceNow;

EmployeeTable:
Load distinct Person,Key
resident tmpEmployeeTable;

 

//There are people with different names in 2 different tables: Daniel J Built and Daniel Built. Both names represent the same person, but Qlik is recognizing them as separate entities. How can I merge both names into 1 name? To represent all the data under one person? 

Labels (1)
2 Solutions

Accepted Solutions
Employee
Employee

Re: Duplicate Values in Merged Coulmn

One way to solve this is to use a mapping table that you can create in the script as a lookup to replace values.

See this article ..

https://community.qlik.com/t5/Qlik-Design-Blog/Data-Cleansing/ba-p/1464362

 

View solution in original post

Employee
Employee

Re: Duplicate Values in Merged Coulmn

You could also use the RENAME function at the end of the script to do a lookup on a mapping table which is after the load statement and will cover any tables.

Rename Field (using mapname | oldname to newname{ , oldname to newname })

Rename Fields (using mapname | oldname to newname{ , oldname to newname })

 

View solution in original post

4 Replies
Employee
Employee

Re: Duplicate Values in Merged Coulmn

One way to solve this is to use a mapping table that you can create in the script as a lookup to replace values.

See this article ..

https://community.qlik.com/t5/Qlik-Design-Blog/Data-Cleansing/ba-p/1464362

 

View solution in original post

Sumnima
New Contributor

Re: Duplicate Values in Merged Coulmn

Hey, thank you for the link, its a great resource. However, the link works for duplicate values loaded from the same table but my duplicate values are loaded from/because of 2 separate tables. Is there anything I could do about it? 

Employee
Employee

Re: Duplicate Values in Merged Coulmn

You could also use the RENAME function at the end of the script to do a lookup on a mapping table which is after the load statement and will cover any tables.

Rename Field (using mapname | oldname to newname{ , oldname to newname })

Rename Fields (using mapname | oldname to newname{ , oldname to newname })

 

View solution in original post

Sumnima
New Contributor

Re: Duplicate Values in Merged Coulmn

Thank you