topic Create mappingsheet to combine customers in QlikView

Create mappingsheet to combine customers

jjordaan — Tue, 23 Oct 2012 15:19:24 GMT

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.

Re: Create mappingsheet to combine customers

Gysbert_Wassenaar — Tue, 23 Oct 2012 18:00:19 GMT

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.

Re: Create mappingsheet to combine customers

jjordaan — Fri, 26 Oct 2012 19:05:52 GMT

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

Re: Create mappingsheet to combine customers

Gysbert_Wassenaar — Sat, 27 Oct 2012 08:30:15 GMT

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.

Re: Create mappingsheet to combine customers

jjordaan — Sat, 27 Oct 2012 09:35:36 GMT

Gysbert,

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

Re: Create mappingsheet to combine customers

jjordaan — Sat, 27 Oct 2012 09:52:46 GMT

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.

Re: Create mappingsheet to combine customers

Gysbert_Wassenaar — Sat, 27 Oct 2012 14:00:43 GMT

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.