topic Re: Duplicate Values in Merged Coulmn in QlikView

Duplicate Values in Merged Coulmn

Sumnima — Sat, 16 Nov 2024 03:08:54 GMT

//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?

Re: Duplicate Values in Merged Coulmn

Lisa_P — Mon, 01 Jul 2019 05:17:12 GMT

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

Re: Duplicate Values in Merged Coulmn

Sumnima — Mon, 01 Jul 2019 18:05:43 GMT

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?

Re: Duplicate Values in Merged Coulmn

Lisa_P — Mon, 01 Jul 2019 23:19:24 GMT

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 })

Re: Duplicate Values in Merged Coulmn

Sumnima — Tue, 02 Jul 2019 14:24:31 GMT

Thank you