Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

paulyeo11
Valued Contributor II

How to clean my data , when company have 2 different name ?

Hi All

My raw data for company in 1997 and 1998 , the company name was

 

AT MECHATRONICS P L

After year 19999 , the name change to below

AT MECHATRONICS PTE LTD

May i know what it the best way to solve this issue ? So that both company name will be same.

Hope some one can suggest the best approach.

company with 2 name.png

1 Solution

Accepted Solutions
shankarece
Honored Contributor

Re: How to clean my data , when company have 2 different name ?

Hi Paul,

This is one of the common problem across many industries. You can create a mapping table as long as Cust ID is same for the two different common name. I see from your screenshot, though the name is different, the Cust ID is same.

So please find the steps here How to Rename Fields using Mapping Table to solve this issue.

Thanks.

Siva

8 Replies

Re: How to clean my data , when company have 2 different name ?

May be this? I assume, you may have Year field with you. Assume typo error for 19999 instead of 1999

If(year >= 1999 and Match(COMPANY,'AT MECHATRONICS P L'),'AT MECHATRONICS PTE LTD', COMPANY)

shankarece
Honored Contributor

Re: How to clean my data , when company have 2 different name ?

Hi Paul,

This is one of the common problem across many industries. You can create a mapping table as long as Cust ID is same for the two different common name. I see from your screenshot, though the name is different, the Cust ID is same.

So please find the steps here How to Rename Fields using Mapping Table to solve this issue.

Thanks.

Siva

paulyeo11
Valued Contributor II

Re: How to clean my data , when company have 2 different name ?

Hi Siva

Yes , you are correct . This is best approach.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>

sonysree88
Contributor

Re: How to clean my data , when company have 2 different name ?

Hi Paul,

So you want to change the first name AT MECHATRONICS P L as also be a second name AT MECHATRONICS PTE LTD or what?

paulyeo11
Valued Contributor II

Re: How to clean my data , when company have 2 different name ?

Hi Sir

I want to change both coy name end with Pte Ltd

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>

sonysree88
Contributor

Re: How to clean my data , when company have 2 different name ?

Hi ,

This is the way we can replace the name :

Replace.png

map1:

mapping load * inline [

x, y

happy, ''

dinner, ''

] ;

Directory;

LOAD

SaelsOrder_ID,

SalesOrder_BP,

SalesOrder_Text,

mapsubstring('map1',SalesOrder_Text) as Changed

FROM

[152744.xlsx]

(ooxml, embedded labels, table is Sheet1);

Here as your requirement you can change as below replace(

Maptable:

x, y

AT MECHATRONICS PTE LTD, 'AT MECHATRONICS PTE LTD'

AT MECHATRONICS P L, 'AT MECHATRONICS PTE LTD'

] ;


Regards,

Sony

shankarece
Honored Contributor

Re: How to clean my data , when company have 2 different name ?

Hi Paul,

Please mark it as correct answer and close the thread. It help others.

Siva

paulyeo11
Valued Contributor II

Re: How to clean my data , when company have 2 different name ?

Sure I will do it.

Paul Yeo

DIrector

TDS Technology (S) P/L

Whatsapp +65 9326 1804

www.tdstech.com<http://www.tdstech.com>