topic Lookup ? in QlikView

Lookup ?

Tue, 15 Feb 2011 16:18:13 GMT

I need some input. I have a table with customers and the problem is that one customer can exist more than one time. This due to different spelling in customer name, or adress or...

I have made a simple demo for this. You can se an image below.

How can I use the lookup function to only get one Customer name per customer ID?

Best regards

Anders

Lookup ?

vgutkovsky — Wed, 16 Feb 2011 02:15:25 GMT

Just to make sure it's clear, the reason you're seeing multiple rows is because of the City dimension, not the Customer Name. But if you want to make sure you only have 1 customer name, you can use a maxstring within a Group By, as follows:

LOAD
CustomerID
maxstring([Customer Name]) as [Customer Name Final]
RESIDENT mydata
GROUP BY CustomerID;

Then drop out the old Customer Name and rename the new field to Customer Name.

Regards,

Lookup ?

Wed, 16 Feb 2011 09:22:19 GMT

In the example it´s the city dimension, but it can be customer name, city, adress. The "problem" is that the databas consists of multiple records of one customer due to different customer adresses or mistakes in spelling ...

QlikView reads all unique instances of customer records, leading to multiple rows. IKEA for example, can exist 10 times because of different adresses and/or cities. In this case it´s only interesting to see one customer ID one time, regardless of how many times Customer ID occurs with different spelling in name, adress, city ...

I will try maxstring and se if it does the trick!

Regards, Anders

Lookup ?

prieper — Wed, 16 Feb 2011 09:52:53 GMT

Looking at the data with also different spelling for the cities (guess that Gbg and Göteborg are the same, etc.), you rather should go through the process of creating (manually) a mapping-table of customernames and cities and use it then for cleansing your data. Doing this in Excel with autofilters is a relatively easy task.

HTH
Peter

Lookup ?

Thu, 17 Feb 2011 09:45:38 GMT

I guess that creating a mapping table could work, but i think that in this case it´s to much work to maintain (1000-1500 customers).

Lookup ?

prieper — Thu, 17 Feb 2011 11:28:34 GMT

If there is no discipline with your clients, suggest to invest in cristall bowls.

Peter

Lookup ?

Mon, 21 Feb 2011 14:04:17 GMT

Found a solution after having ignored the problem for a few days.

In this case lookup on CustomerName and CustomerName only returns the first hit, and that works fine for now.

/Anders