12 Replies Latest reply: Dec 23, 2016 8:20 AM by Bill Markham RSS

    Connecting up two different tables to a third table using the same key

    James Hanify

      Hi all,

       

      The structure of our data is something like

       

      Account can have a salesperson

      Invoice can have a salesperson

       

      Salesperson has an id which is linked via account and invoice, and that id has a name next to it, what I would rather not do is do an apply map as there is additional data as part of this table and I believe applymap only just vlookups the id against a descriptor.

       

      Otherwise it creates then syns, how would you recommend I bypass this problem? As syns I presume are not a great thing to have? As they seem to be junction tables to turn a many to many into a 1 to many relationship. Otherwise I am just creating a brand new table with the same data but a different name to get this working and it feels inefficient.

       

      Many thanks,

       

      James.

        • Re: Connecting up two different tables to a third table using the same key
          vj kri

          Hi,

           

          Salesperson table has id which is used to connect with two other tables Account ,Invoice?There are three table to join with same id field? You use below logic


          Salesperson:

          Load

                id,

                id as Salesperson_invoice_id  //Create a new Field with same id and rename to use to relate with Invoice

          FROM Salesperson;


          use id column to connect to Account and Salesperson_invoice_id to connect to Invoice. Let me know if the problem is solved.


          • Re: Connecting up two different tables to a third table using the same key
            Andrew Whitfield

            Hi James,

             

            are you able to post your load script contents?

             

            Andy

              • Re: Connecting up two different tables to a third table using the same key
                James Hanify

                Hi Andrew,

                 

                The appropriate parts are:

                 

                Invoice:
                
                
                LOAD 
                    //Linked keys
                    "Z_ID" as InvoiceKey,
                    If(ORD_NO <> '',COMPANY&'*'&WAREHOUSE&'*'&ORD_NO,) as OrderKey,
                    If(CREDIT_NOTE <> '',COMPANY&'*'&WAREHOUSE&'*'&CREDIT_NOTE,) as CreditKey,
                    CUSTOMER as AccountKey,
                    CUSTOMER as ContactKey,
                    IN_TRANNO as InventoryTransactionKey,
                    AR_TRANNO as AccountsReceivableTransactionKey,
                    //Data
                     'N' as InvoiceArchived, // Some dictionaries being used include SM.INVOICE.PRT and SM.INVOICE.BU
                    TRANDATE as TransactionDate,
                    INV_NBR as InvoiceNo,
                    WAREHOUSE as Warehouse,
                    Left(INV_NBR,1) as InvoiceType,
                    ApplyMap('Mapping_InvoiceType', Left(INV_NBR,1) ,'Unknown') as InvoiceTypeDescription,
                    CARRIER as Carrier,
                    ApplyMap('Mapping_InvoiceCarrier', CARRIER, null()) as CarrierDescription,
                    SALESM_CODE as InvoiceSalesPersonKey,
                    ORDER_TYPE as OrderType,
                    ApplyMap('Mapping_InvoiceOrderType', ORDER_TYPE, null()) as OrderTypeDescription,
                    OPER as DespatchOperator,
                    ApplyMap('Mapping_User',OPER,ApplyMap('Mapping_User2',OPER,null())) as DespatchOperatorName,
                    DATE as DespatchDate,
                    TIME as DespatchTime,
                    GTOTAL as TotalExVat,
                    FREIGHT as TotalFreight,
                    OTH_CHARGES as TotalOtherCharges,
                    TAX_TOTAL as TotalTax,
                    INVOICE_TOT as TotalInvoice;
                    
                SQL SELECT *
                FROM SM_INVOICE WHERE TRANDATE >=  '$(vGenDate)';
                
                
                
                Account:
                LOAD
                   Z_ID as AccountKey,
                   CUST_NAME as AccountName,
                   If(len(CUSTDEF)<1,null(),CUSTDEF) as AccountType,
                   ApplyMap('Mapping_AccountType', CUSTDEF, null()) as AccountTypeDescription,
                   If(len(SIC07_CODE)<1,null(),SIC07_CODE) as AccountSicCode,
                   If(len(REG_NO_C)<1,null(),REG_NO_C) as AccountRegistrationNo,
                   If(len(SALESM_CODE)<1,'HA',SALESM_CODE) as AccountSalesPersonKey,
                   If(len(REC_C)<1,null(),REC_C) as AccountRegionalEducationalConsultantNo;
                   
                   SQL SELECT *
                FROM "AR_MASTER";
                
                SalesPerson:
                LOAD
                Z_ID as InvoiceSalesPersonKey,
                Z_ID as AccountSalesPersonKey,
                if(len(SALESM_NAME)<1,ApplyMap('Mapping_User',USER_ID_C,ApplyMap('Mapping_User2',USER_ID_C,null())),SALESM_NAME) as SalesPersonName, // trying to ensure we have the best chance of grabbing a name
                If(len(SALESMAN_GROUP_C)<1,'SALESTEAM',SALESMAN_GROUP_C) as SalesPersonGroupKey;
                    
                SQL SELECT *
                FROM "SM_SALESMAN";
                
                  • Re: Connecting up two different tables to a third table using the same key
                    vj kri

                    The reason for the above error is

                    Table Invoice and Table Account are connected using AccountKey and SalesPerson is also connected btw the tables Invoice,Account using InvoiceSalesPersonKey,InvoiceSalesPersonKey.

                     

                    The relation btw Invoice and Table Account is not required as they are indirectly connected using SalesPerson filed(InvoiceSalesPersonKey,InvoiceSalesPersonKey.)

                    you can rename one of the AccountKey in one of the tables Invoice,Account

                • Re: Connecting up two different tables to a third table using the same key
                  Andrew Whitfield

                  Cool,

                   

                  can you post a screenshot from Table viewer?

                   

                  Andy

                  • Re: Connecting up two different tables to a third table using the same key
                    Andrew Whitfield

                    Presume it's this bit

                     

                    Z_ID as InvoiceSalesPersonKey, = change this eg as SalesPersonKey

                    Z_ID as AccountSalesPersonKey, = get  rid of this

                     

                    If(len(SALESM_CODE)<1,'HA',SALESM_CODE) as AccountSalesPersonKey, = change to SalesPersonKey

                    SALESM_CODE as InvoiceSalesPersonKey,  = change to SalesPersonKey

                    • Re: Connecting up two different tables to a third table using the same key
                      Bill Markham

                      It looks like you have 2 roles that a person can perform :

                      • Account Manager
                      • Sales

                       

                      For your visualisation requirements is there any need to have person as a dimension and have expressions relevant to both roles ?

                       

                      I would suspect not as this would probably not return logically results.