Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jjordaan
Partner - Specialist
Partner - Specialist

Create mappingsheet to combine customers

Hi QlikViewers,

For a internal project I need to combine data from 4 different databases.

In all the databases customers are stored. For 90% it are the same customers only with different ID's en sometimes different names.

I was thinking to create a mappingsheet in Excel wich the employees can map the customers to each other and then I can import that in QlikView.

Does anyone has a better idea?

Thank you for your help.

6 Replies
Gysbert_Wassenaar

Sounds like a good idea. You'll need a 'clean' customer table too of course that contains only the deduplicated customers with the correct id numbers. You'll probably want 4 mapping tables, one for each source database to be sure you map the customer id's to the correct new id's of the deduped customers.


talk is cheap, supply exceeds demand
jjordaan
Partner - Specialist
Partner - Specialist
Author

Gysbert,

What do you think that is the best way to create a unique Customer Name.

In every database there is a CustomerName, but most of the names are spelled different.

So we have said that our financial system is leading in terms of their names.

It could be that there are prospects in the CRM system but not in the financial system.

What would be the best method that if the customer is in the financial system we want to use that name, otherwise the name of the customer in the CRM system

If the client occurs in system a and system b then they will be linked via the mapping table and is the leading name the name in the financial system.

I hope you can help with this.

Thank you in advance

Gysbert_Wassenaar

Well, if you don't have a proper data cleansing tool at hand, you could put all customers from all sources in one table (preferably inclusive address data) and do a load distinct from that to get rid of all the duplicates. Then you'll have to clean up the list. You can use a sound matching algorithm like metaphone to calculate a field from customer names. Similar names will get the same value so you can match on that. And if you have address data for the customers you can use those for matching too. In the end you'll have a list with duplicate candidates. That list you'll have to clean up manually, since you will have to decide if they're really dupes and which one you want to keep and which one should go.


talk is cheap, supply exceeds demand
jjordaan
Partner - Specialist
Partner - Specialist
Author

Gysbert,

Thanks again! I didn't know the function Metaphone in QlikView or SOUNDEX() inT-SQL.

jjordaan
Partner - Specialist
Partner - Specialist
Author

Gysbert,

Ik zou graag wat meer willen leren over ETL en dit toepassen. Zeker op het gebied van data opschoning.

Heb jij nog tips over een bepaald product (SSIS) en hoe ik dit het beste kan leren?

Alvast bedankt.

Gysbert_Wassenaar

Oei, nu komen de echt moeilijke vragen

Ik ken SSIS zelf niet, al heb ik er wel mensen op horen schelden.

Persoonlijk ben ik iemand die op bijvoorbeeld Amazon een of meer boeken uitzoekt en dan gaat lezen om daarna met de (trial)software aan de slag te gaan.


talk is cheap, supply exceeds demand