6 Replies Latest reply: Feb 21, 2011 9:04 AM by anderska RSS

    Lookup ?

      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.

      error loading image

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

      Best regards

      Anders

        • Lookup ?
          Vlad Gutkovsky

          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 ?

              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 ?
                  Peter Rieper

                  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