6 Replies Latest reply: May 24, 2018 1:33 AM by Sumanta Kumar Mandal RSS

    Joining multiple tables

    amit goenka

      Hello Community,

       

      Please help with the following:

       

      I have 3 tables as follows:


      Sales1:

      Month

      CustomerID

      CustomerType

      Revenue


      Sales2:

      Month

      CustomerAccountNo

      CustomerType

      Revenue


      As observed, the above two transactional tables contain revenue figures per month per customer, but have different customer identifiers (CustomerID is Sales1 and CustomerAccountNo in Sales2). The Month values in Sales1 and Sales2 are same, just that the CustomerType values are different.


      In addition, I have two state tables that describe the customers:


      Customers:

      CustomerID

      CustomerAccountNo

      CustomerType

      CustomerAddress

      Age


      SpecialCustomers:

      CustomerID

      CustomerAccountNo

      CustomerGroup


      Data sets are incomplete, so:

      a. Customers table does not contain all Customer info (some SpecialCustomers are not mapped in Customers table)

      b. Some Customers in Sales1 and Sales2 are not mapped in Customers

      c. All Customers in Customers are not present in Sales1 and Sales2


      How do I join (concatenate or link) these tables to have 1 or more associated tables so that I can have filters by CustomerType, Age, CustomerGroup, <other fields>, and present Revenue reports.


      I have nulls for certain fields so will have to use set NullValue as well in the script, else few charts will show expression totals against nulls. (Example: Revenue by Age: for cases where a CustomerID is Sales1 but not in Customers will show a number against a Null age)


      Thanks for the help.


      Cheers

      A

        • Re: Joining multiple tables
          Eduardo DImperio

          Can you put some Input and Output example, to make easier define a join?

            • Re: Joining multiple tables
              amit goenka

              HI Eduardo,

               

              I have enclosed sample data set below:

                   

              Sales1:
              MonthCustomerIDCustomerTypeRevenue
              JanID1Retail110
              JanID2Retail120
              JanID3Retail1320
              JanID4Retail1400
              Sales2:
              MonthCustomerAccountNoCustomerTypeRevenue
              JanAC1Retail250
              JanAC2Retail260
              JanAC3Retail270
              JanAC4Retail280
              Customers:
              CustomerIDCustomerAccountNoCustomerTypeCustomerAddressAge
              ID1AC10Retail1ABC21
              ID2AC20Retail1DEF26
              ID3AC30Retail1GHI28
              ID4AC1Retail2JK27
              ID5AC2Retail2MN24
              ID6AC3Retail2OP22
              ID7AC7Retail1EE25
              ID8AC8Retail2RR28
              SpecialCustomers:
              CustomerIDCustomerAccountNoCustomerGroup
              ID1AC10G1
              ID4AC1G1
              ID90AC90G2
              ID100AC100G2

               

              As you can observe, the table data is not all inclusive.

               

              The output could be in one of the following ways:

              a. A combined table "Sales" with Sales1 and Sales2 data, containing all fields from both tables. Nulls could be marked with <No Value>.

              b. A combined table "AllCustomers" with Customers and SpecialCustomers data, identifying the group of the customer. Nulls could be marked with <No Value>.

              c. Associative link between Sales and AllCustomers table.

               

              The process may involve intermediate steps and that works for me.

               

              Please advise.

               

              Thanks

                • Re: Joining multiple tables
                  Eduardo DImperio

                  Hi Amit,

                   

                  Try This

                   

                  Sales:

                  Load

                  *

                  Resident Sales1

                   

                  Concatenate (Sales)

                   

                  Load

                  *

                  Resident Sales2

                  //*******************************************************************

                  Left Join(Sales)

                  Load

                  *

                  Resident Customers

                   

                  Left Join(Sales)

                  Load

                  *

                  Resident SpecialCustomers


                  //*******************************************************************

                  NoConcatenate

                  Sales_Table:

                  LOAD

                  CustomerID,

                  CustomerType,

                  Revenue,

                  Month,

                  CustomerAccountNo,

                  IF(ISNULL(CustomerGroup),'No Value',CustomerGroup) AS CustomerGroup,

                  IF(ISNULL(CustomerAddress),'No Value',CustomerAddress) AS CustomerAddress,

                  IF(ISNULL(Age),'No Value',Age) AS Age,

                  RESIDENT Sales;


                  Drop table Sales

                   

                  • Re: Joining multiple tables
                    Sumanta Kumar Mandal

                    MAPPING_TAB1:

                    MAPPING LOAD

                    CustomerID&'-'&CustomerType AS CUST_ID_TYPE,

                        CustomerAccountNo

                    FROM

                    [C:\Users\SumantaM\Desktop\SALES_CUST.xlsx]

                    (ooxml, embedded labels, table is CUSTOMERS);

                     

                     

                    MAPPING_TAB2:

                    MAPPING LOAD

                    CustomerAccountNo&'-'&CustomerType AS CUST_ACCNO_TYPE,

                    CustomerID

                    FROM

                    [C:\Users\SumantaM\Desktop\SALES_CUST.xlsx]

                    (ooxml, embedded labels, table is CUSTOMERS);

                    ===================================================================

                     

                    TMP_SALES:

                    LOAD Month,

                         CustomerID AS CUST_ID,

                         CustomerType AS CUST_TYPE,

                         APPLYMAP('MAPPING_TAB1',CustomerID&'-'&CustomerType,'') AS CUST_ACC_NO,

                         Revenue

                    FROM

                    [C:\Users\SumantaM\Desktop\SALES_CUST.xlsx]

                    (ooxml, embedded labels, table is SALES1);

                     

                     

                    CONCATENATE(TMP_SALES)

                     

                     

                    LOAD Month,

                         CustomerAccountNo AS CUST_ACC_NO,

                         CustomerType AS CUST_TYPE,

                         APPLYMAP('MAPPING_TAB2',CustomerAccountNo&'-'&CustomerType,'') AS CUST_ID,

                         Revenue

                    FROM

                    [C:\Users\SumantaM\Desktop\SALES_CUST.xlsx]

                    (ooxml, embedded labels, table is SALES2);

                     

                     

                    NOCONCATENATE

                    SALES:

                    LOAD *,

                    CUST_ID&'-'&CUST_ACC_NO&'-'&CUST_TYPE AS CUST_ID_ACCNO_TYPE

                    RESIDENT TMP_SALES;

                     

                     

                    DROP TABLE TMP_SALES;

                     

                    ==============================================================================

                     

                    TMP_CUSTOMER:

                    LOAD CustomerID&'-'&CustomerAccountNo AS CUST_ID_ACCNO,

                    CustomerID,

                    CustomerAccountNo,

                         CustomerType,

                         CustomerAddress,

                         Age

                    FROM

                    [C:\Users\SumantaM\Desktop\SALES_CUST.xlsx]

                    (ooxml, embedded labels, table is CUSTOMERS);

                     

                    JOIN(TMP_CUSTOMER)

                     

                    LOAD CustomerID&'-'&CustomerAccountNo AS CUST_ID_ACCNO,

                         CustomerGroup

                    FROM

                    [C:\Users\SumantaM\Desktop\SALES_CUST.xlsx]

                    (ooxml, embedded labels, table is [SPECIAL CUSTOMERS]);

                     

                     

                    NOCONCATENATE

                    CUSTOMER:

                    LOAD *,

                    CustomerID&'-'&CustomerAccountNo&'-'&CustomerType AS CUST_ID_ACCNO_TYPE

                    RESIDENT TMP_CUSTOMER;

                     

                     

                    DROP TABLE TMP_CUSTOMER;