Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
james_hanify
Creator
Creator

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

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.

12 Replies
parimikittu
Creator II
Creator II

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.


james_hanify
Creator
Creator
Author

Hi, I have tried that and got the following:

One or more loops have been detected in your database structure. Loops may cause ambiguous results and should therefore be avoided. QlikView will cut the loop(s) by setting one or more tables as loosely coupled. Settings for loosely coupled tables can be modified after script execution in the tables page of the document properties dialog.

parimikittu
Creator II
Creator II

Can you please past your code. Will check and advise the correction

awhitfield
Partner - Champion
Partner - Champion

Hi James,

are you able to post your load script contents?

Andy

james_hanify
Creator
Creator
Author

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

awhitfield
Partner - Champion
Partner - Champion

Cool,

can you post a screenshot from Table viewer?

Andy

parimikittu
Creator II
Creator II

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

awhitfield
Partner - Champion
Partner - Champion

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

james_hanify
Creator
Creator
Author

Hi Andrew,

If I do that it then gets back to the $SYN tables

syn.png

The problem is Invoice and account can have two different salespeople attached but the data they pull from key wise is the same.

And with having Account and Invoice SalespersonKey

qlik.png