Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
Can you please past your code. Will check and advise the correction
Hi James,
are you able to post your load script contents?
Andy
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";
Cool,
can you post a screenshot from Table viewer?
Andy
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
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
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