Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
ContactNo | Name | CustomerNo | VendorNo |
---|---|---|---|
CT001 | Name A | CUS0022 | |
CT002 | Name B | VEN0025 | |
CT003 | Name C | CUS0024 |
Entry No | Item No | Source Type | Source No |
---|---|---|---|
1 | I123 | Vendor | VEN0025 |
2 | I456 | Customer | CUS0022 |
Try loading your contacts in two batches to fill a field "Source No" so you can associate your tables properly.
Contacts:
LOAD
"No_" as ContactNo,
Name,
"Customer No_" as CustomerNo,
"Vendor No_" as VendorNo,
"Customer No_" as "Source No";
SQL SELECT
"No_",
Name,
"Customer No_",
"Vendor No_"
FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact"
WHERE "Customer No_" IS NOT NULL;
LOAD
"No_" as ContactNo,
Name,
"Customer No_" as CustomerNo,
"Vendor No_" as VendorNo,
"Vendor No_" as "Source No";
SQL SELECT
"No_",
Name,
"Customer No_",
"Vendor No_"
FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact"
WHERE "Vendor No_" IS NOT NULL;
SQL SELECT
"No_",
Name,
"Customer No_",
"Vendor No_"
FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact";
Entries:
LOAD
"Entry No_",
"Item No_",
"Posting Date",
"Document No_",
"Source No_" as "Source No"
SQL SELECT
"Entry No_",
"Item No_",
"Posting Date",
"Document No_",
"Source Type",
"Source No_"
FROM $(@DatabaseName).dbo."$(@CompanyName)$Value Entry";
Ty for your help. I forgot to mention that it's possible that one contact has CustomerNo aswell as VendorNo filled in. Your method doesn't give good results in that situation.
Hi,
Change your IF statement
IF( "Source Type" = '1', "Source No_") as CustomerNo,
IF( "Source Type" = '2', "Source No_") as VendorNo;
TO
IF( "Entry No_" = '1', "Source No_") as CustomerNo,
IF( "Entry No_" = '2', "Source No_") as VendorNo;
"Source Type" are "Vendor" or "Custumer".
NOT "1" or "2"
You need to have two Table Entries:
One for "Vendor" and other for "Custumer"
Make a join between Contacts and Entries_Vendor
After between result join with Entries_Custumer.
Ex.:
NewTable:
NoConcatenate
LOAD
ContactNo,
Name,
CustomerNo,
VendorNo
Resident Contacts;
JOIN (NewTable)
LOAD
"Entry No_",
"Item No_",
"Posting Date",
"Document No_",
CustomerNo
// VendorNo
Resident Entries
WHERE Source Type = '1';
JOIN (NewTable)
LOAD
"Entry No_",
"Item No_",
"Posting Date",
"Document No_",
// CustomerNo
VendorNo
Resident Entries
WHERE Source Type = '2';
Drop table Entries;
Drop table Contacts;