Lookup ?

[#1]

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

[vgutkovsky]

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,

[#3]

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

[prieper]

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

[#5]

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

[prieper]

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

++

Peter

[#7]

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