I have a scenario like this:
1. SalesRegister table with OrdersourceCustId, InvoicesourceCustId and DataareaId
2. Customer Master Table with sourcecustid and custsource(same as DataAreaId)
So, now what i have to do is,
Using OrdersourceCustId and DataareaId from SalesRegister table I have to Link sourcecustid and custsource from CustomerMaster to pull SHipTo information(Because shipto is related to OrdersourceCustId)
And
Using InvoicesourceCustId and DataareaId from SalesRegister table I have to Link sourcecustid and custsource from CustomerMaster to pull BillTo information(Because BillTo is related to InvoicesourceCustId)
The tables and the way i was trying to make the links are given below. The links are not working the way i am trying. Any help would be very appreciated.
CustomerMaster:
LOAD custid,
custsource,
sourcecustid,
custName,
AddrLine1,
City,
State,
ZipCode,
country,
FamilyName,
FamilyGroup,
SalesGroup,
TaxId
FROM
[\Customer.qvd]
(qvd)
;
ShipToAddress:
LOAD AddrLine1 as ShipToAddress,
custsource,
sourcecustid&'|'&custsource as %Key,
City as ShipToCity,
country as ShipTocountry,
country as [Sales Customer Country],
custName as ShipToCustName,
SalesGroup as [Customer Sales Group],
sourcecustid as OrdersourceCustId,
State as ShipToState,
ZipCode as ShipToZipCode,
TaxId as ShipToVAT
FROM
[\Customer.qvd]
(qvd)
;
BillToAddress:
LOAD AddrLine1 as BillToAddress,
custsource,
City as BillToCity,
country as BillTocountry,
custName as BillToCustName,
custsource as BillToCustSource,
sourcecustid as InvoicesourceCustId,
sourcecustid&'|'&custsource as %Key,
State as BillToState,
ZipCode as BillToZipCode,
TaxId as BillToVAT
FROM
[\Customer.qvd]
(qvd)
;
SalesRegister:
LOAD
DataareaId,
OrdersourceCustId&'|'&DataareaId as %Key,
[Invoice Date],
InvoicesourceCustId,
InvoiceCustomerGroup,
ItemID,
LineAmount,
Linenum,
OrdersourceCustId,
OrdersourceCustId as sourcecustId,
OrderCustomerGroup,
Qty,
VAT
FROM
[\SalesRegister.qvd]
(qvd)
;
Thanks,
Shan
