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]

Without knowing your data or your script I am not sure if this is usefull but did you try to create a key to conect both databases.

Something like:

[Customer Table] &'-' &[Order table] as KEYFIELD,

(or any other fields you want to connect)

Hope this is helpfull

Dennis.

[#3]

I am not sure if I understand creating a like like that. I know I can use the & '-' & as field to rename fields so they can link, but my issue is that I now have 2 tables that connect to each other in 2 ways. All orders in the old database connect with custcode and all orders in the newest database connect with custnum. Custnum is not in the old database and custcode is not in the new database.

Unless you think that doing something like this would work? Company and custnum do not exist in the data from old database so it would only pass custcode and in the new would only pass company and custnum?

company& custnum & custcode as link

Edit: Now that I thik about it I am not sure how to create this link, because in the script I have 4 tables. They are not combined until the script is run, therefor I can not make the link I described above?

[Anonymous]

Is there a way you can share your source with use. Maybe make two Excel files with the same information in it?

Then I can have a look.

[#5]

Attached is a sample excel book. each sheet is a different table.

Used this load script:

Customer:

LOAD Name,

     custnum

FROM

...

(ooxml, embedded labels, table is Customer);

Order:

LOAD custnum,

     [order],

     orderamt

FROM

...

(ooxml, embedded labels, table is Order);

Cutomer2:

Concatenate(Customer)

LOAD Name,

     custcode

FROM

...

(ooxml, embedded labels, table is Oldcust);

Order2:

Concatenate(Order)

LOAD custcode,

     Ordernum,

     orderamt

FROM

...

(ooxml, embedded labels, table is OldOrder);

[Anonymous]

What you could do first is join the two pair (old with old and main with main)

for example like this:

Main:

LOAD Name,

     custnum

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Customer);

Join (Main)

LOAD custnum,

     [order],

     orderamt

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Order);

Old:

LOAD Name as NameOld,

     custcode

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Oldcust);

Join (Old)

LOAD custcode,

     Ordernum,

     orderamt as OrderAmtOld

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is OldOrder);

No you have two tables one old and one main.

You can connect them on any field you want.

[Anonymous]

This will connect them on the name field:

Main:

LOAD Name,

     custnum

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Customer);

Join (Main)

LOAD custnum,

     [order],

     orderamt

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Order);

Old:

LOAD Name,

     custcode

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is Oldcust);

Join (Old)

LOAD custcode,

     Ordernum,

     orderamt as OrderAmtOld

FROM

[Book1(1).xlsx]

(ooxml, embedded labels, table is OldOrder);

This makes the most sense to me, but maybe you want it on an other field ...

[SunilChauhan]

see the attached file.

hope this help

[#9]

OK so say I want to show the Monthly order amount for both databases? I was achieving this by linking the order dates from each order table together.

Your example works when I need to see the total ordered per customer without the date information.

So at the moment I am stuck either connecting the customers together, or stuck with linking just the orders together.

[#10]

I guess I did not explain enough. My bad.

I was trying to combine customers and combine orders because I need to look at the data in different ways.

IE:

Total order amount per customer (Works when I link the customers together and then separately link the orders to each customer)

total orderamt per month (Only works when I combine orderamt and orderdates from the order table)

My issue is I need both to work, so I need all order dates from both tables in one field to use as a dimension, and then the sum of orderamt as a expression.

Then I also need all customers in one field for a dimension and the sum of orderamt to use as the expression