Forum Discussion
Helper IIIHow do I combine items with different names?
I have a report that shows the sales of customers. Over time the name of the customer has changed however our database retains the name at the time of the order being placed for histroical purposes.
So I have 3 different company names over the last 5 years, however they are all one and the same customer, just different headers.
I have tried simply replacing the values for the different names to the same, however I then get a lot of blank records.
Any other suggestions?
Thanks
Craig
12 Replies
CraigBlackman You could create a seperate Dimension table that rolls the names up to the parent company, in that respect it might be easier to determine missing companies that may be causing your blanks as well. So every company would be in your dimension table, and some would roll up to a parent and others would roll up to themselves...
Something like:
company Parent
A 1
B 1
C 2
D 3
CraigBlackmanAnd how would I do that exactly?
Thanks for you help.
What is your backend, SQL?
Can you show a sample of what your data looks like now?
Ideally, I would try to do this on the database side, is that possible in this case?
- kcantor
Community Champion
Use the Querry Editor to replace the name in the data after it is uploaded. Use the customer account number to pull the numbers. Create a calculated column building on an if statement so that all other customer names are pulled in without change. That customer's name is changed. Then, use that column for building reports displaying name.
GTRI would also suggest looking into type 2 slowly changing dimensions in your data model. Hopefully this leads you in the right direction as an alternative if the other ideas do not work out.
