20 Replies Latest reply: Sep 27, 2011 1:45 PM by Marc Livingston RSS

    Combining data sources. Issue with how tables link in each

      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.

        • Combining data sources. Issue with how tables link in each
          Dennis Hoogenboom

          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.

            • Re: Combining data sources. Issue with how tables link in each

              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?

                • Re: Combining data sources. Issue with how tables link in each
                  Dennis Hoogenboom

                  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.

                    • Re: Combining data sources. Issue with how tables link in each

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

                        • Combining data sources. Issue with how tables link in each
                          Dennis Hoogenboom

                          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.

                            • Combining data sources. Issue with how tables link in each
                              Dennis Hoogenboom

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

                                • Re: Combining data sources. Issue with how tables link in each
                                  Sunil Chauhan

                                  see the attached file.

                                   

                                  hope this help

                                  • Re: Combining data sources. Issue with how tables link in each

                                    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

                                      • Re: Combining data sources. Issue with how tables link in each
                                        Dennis Hoogenboom

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

                                          • Re: Combining data sources. Issue with how tables link in each

                                            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.

                                                • Re: Combining data sources. Issue with how tables link in each

                                                  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.

                                                    • Re: Combining data sources. Issue with how tables link in each
                                                      Sravan Puppala

                                                      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

                                                      [C:\Users\Puppala_S\Desktop\Temp\Forum trials\Book1.xlsx]

                                                      (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

                                                      [C:\Users\Puppala_S\Desktop\Temp\Forum trials\Book1.xlsx]

                                                      (ooxml, embedded labels, table is Customer);

                                                       

                                                      OrderOLd:

                                                      Noconcatenate

                                                      LOAD custcode,

                                                           Ordernum,

                                                           orderamt

                                                      FROM

                                                      [C:\Users\Puppala_S\Desktop\Temp\Forum trials\Book1.xlsx]

                                                      (ooxml, embedded labels, table is OldOrder);

                                                       

                                                       

                                                      Order:

                                                      Noconcatenate

                                                      LOAD custnum,

                                                           [order],

                                                           orderamt as Orderamt1

                                                      FROM

                                                      [C:\Users\Puppala_S\Desktop\Temp\Forum trials\Book1.xlsx]

                                                      (ooxml, embedded labels, table is Order);

                                                      Regards

                                                      Sravan

                                                        • Re: Combining data sources. Issue with how tables link in each

                                                          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.

                                  • Re: Combining data sources. Issue with how tables link in each

                                    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)