topic Re: Connecting up two different tables to a third table using the same key in QlikView

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

james_hanify — Thu, 22 Dec 2016 16:30:17 GMT

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

parimikittu — Fri, 23 Dec 2016 10:59:52 GMT

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

james_hanify — Fri, 23 Dec 2016 11:05:42 GMT

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.

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

parimikittu — Fri, 23 Dec 2016 11:08:08 GMT

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

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

awhitfield — Fri, 23 Dec 2016 11:12:55 GMT

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 — Fri, 23 Dec 2016 11:18:41 GMT

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

awhitfield — Fri, 23 Dec 2016 11:27:53 GMT

Cool,

can you post a screenshot from Table viewer?

Andy

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

parimikittu — Fri, 23 Dec 2016 11:28:48 GMT

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

awhitfield — Fri, 23 Dec 2016 11:32:51 GMT

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

james_hanify — Fri, 23 Dec 2016 11:44:53 GMT

Hi Andrew,

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

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

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

Anonymous — Fri, 23 Dec 2016 12:21:33 GMT

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.

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

james_hanify — Fri, 23 Dec 2016 12:29:38 GMT

An invoice needs to be there for historical significance as an account can be updated. I guess the only way is to duplicate the tables?

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

Anonymous — Fri, 23 Dec 2016 13:20:47 GMT

Your SalesPerson table only has 3 fields :

Z_ID

Name

Group

So you could do a couple of Mapping Loads

Using Z_ID to return Name

Using Z_ID to return Group

Then a couple applymaps whilst loading the Invoice table creating 2 fields

Invoice Sales Person

Invoice Sales Group

And a couple more applymaps for the Account table creating

Account Manager

Account Manager Group

Thus you'll have no SalesPerson table in the resultant data model. ergo no synthetic keys or loops.