Combining data sources. Issue with how tables link in each

[#1]

I am trying to combine 2 databases but I am running into some issues. I have tried Concatenate Loading our old data base in with our new one. I renamed the fields I want to be listed together. My issue arises when I want to go into more detail.

Example:

Main database has Customer Table, and Order table. They are linked using a customer number,

Old database has Customer and order table. They are linked together using custcode(shortname). Customer number only exists in main database.

I am able to combine the 2 customer tables fine using the customer name. I can combine the order tables fine using the order date fields and order amount fields.

The issue is that I now want to link the customers in with their orders, but since each database connects to the orders differently I am getting the wrong results.

I need it to link the old database fields using custcode, and the main database using customer number.

[Anonymous]

Try if this works for you:

Main:

LOAD Name,

     custnum

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Customer);

Join (Main)

LOAD custnum,

     [order] as Ordernum, 

     orderamt

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Order);

Join (Main)

LOAD Name,

     custcode

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Oldcust);

Join (Main)

LOAD custcode,

     Ordernum,

     orderamt

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is OldOrder);

[#3]

This is close, but it still has issues linking the customer names. To see what I mean, create a table and add all fields. You see in the table below that orders 1-7 no longer have customer names. If I create a pivot table  with customer name I receive the second chart

custcode	custnum	Name	orderamt	Ordernum
abc			85	1
adg			67	2
fry			588	3
thu			54	4
edf			135	5
abc			21	6
adg			29	7
abc	1	abcdt	55	55
adg	2	adgdt	22	56
fry	3	frydt	23	57
thu	4	thudt	59	58
abc	1	abcdt	68	59
abc	1	abcdt	128	60
edf	5	edfdt	548	61
adg	2	adgdt	2	62
fry	3	frydt	5644	63
thu	4	thudt	66	64

Name	sum(orderamt)
abcdt	251
adgdt	24
edfdt	548
frydt	5667
thudt	125
-	979

Edit:

I am not familiar with it, but could this be done with mapping?

It seems like the issue is that I need all customers to have both custcode and custnum in the script for this to work correctly.  Currently I only get this information together after the script load using concatenate.

[#4]

Hi

Is this what you want?

[#5]

Can you explain what you did? I have never used temp tables so I need to wrap my mind around how it works.

This also seems to ignore the new/main database order table.

[#6]

Hi

sorry this is what you want to see at the end I guess!

Please see the code.

Ask if you have any doubts. This is all Trial and Error what I did..

Cust:

LOAD Name,

     custcode

FROM

(ooxml, embedded labels, table is Oldcust);

inner Join (Cust) // Inner Join is for loading customers which are present in both Old as well as Main Customer Table but even normal Join also works

LOAD Name,

     custnum

FROM

(ooxml, embedded labels, table is Customer);

OrderOLd:

Noconcatenate

LOAD custcode,

     Ordernum,

     orderamt

FROM

(ooxml, embedded labels, table is OldOrder);

Order:

Noconcatenate

LOAD custnum,

     [order],

     orderamt as Orderamt1

FROM

(ooxml, embedded labels, table is Order);

Regards

Sravan

[#7]

Your example does correctly link the customers, but in with our actual data there are customers that exist in the old data that do not exist in the new, and customers that exist in the new that do not exist in the old. So the inner join will not work since that returns only customers that exist in both.

Also has the order tables separate, where in our actual data I was combining the orderamt fields and the orderdate fields so I could create a chart that shows orderamount by month.

I am going to try a new approach. We have Cystal Reports, so I was able to take the Old database customers and then add the new database as a subreport. I then assigned the custnum to the customers from the old database and exported it to excel.

So now I have an excel file that lists the Old database Customers that now also has a custnum from the new database in the same file.

[#8]

Ok I seem to be loosing track of what I am trying to accomplish.

I have 4 tables,

Customer 2010-present

OldCustomer 2004-2009

Orders 2010-present

OldOrders 2004-2009

I want to combine all data from customers into 1 table. Customers need to be in one field.

I want to combine all data from Orders. Orderdate, orderamt, and order numbers need to be in their respective fields.

Needs to be full outer joined due to data exisiting in both places.

I need to link Combined Customer table with Combined Order Table.

Customer connects to Orders using Company, and custnum.

OldCustomer connects to OldOrders using custcode.

Results:

Would like to see Order amout per month that pulls data from combined order table.

2004 - 2011 by month

Would like to see Customer orders by year/ month in a pivot using combined customer table and combined order table.

Customer Name, Year Ordered, Month Ordered, Order Amount

ABC,2004,3,15         (From Old Orders)

ABC,2006,2,25        (From Old Orders)

ABC,2007,1,55             (From Old Orders)

ABC,2007,2,86              (From Old Orders)

ABC,2007,3,455          (From Old Orders)  

ABC,2008,11,55            (From Old Orders) 

ABC,2010,9,64            (From Orders)

ABC,2011,7,51              (From Orders)

[#9]

Hi

You can only concatenate to make one CustomerTable from the following Tables as they may have different customers also.

Customer 2010-present

OldCustomer 2004-2009

with out empty fields it is not possible just for Info.

[#10]

I was able to combine the tables just fine. I now Have a working list of customers from Both data sources.

I also Have a working chart of orderamt by year.

My only problem is linking the customer tables with the order tables since the databases do the linking differently.

I can't take a customer and see orders from both datasources

[john_duffy · Accepted solution]

Hi Marc.

How about creating a new key field on the two tables.  When combining the current and old customer tables, create a new field called Customer_Orders_Key.  Records from the current customer table will create this field using Company & Cust_Num.  Records from the old customer table will create this field using CustCode.

Do the same when combining the current and old order tables.

Now link the combined customers table to the combined orders table using Customer_Orders_Key.