Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with join

I have this weird problem with a join:
Contacts:
ContactNoNameCustomerNoVendorNo
CT001

Name A

CUS0022
CT002Name BVEN0025
CT003Name CCUS0024
To join with table Entries:
Entry NoItem NoSource TypeSource No
1I123VendorVEN0025
2I456CustomerCUS0022
So I want to add the customer or vendor name to the entries. When I use this script it doesn't work, it just adds the contacts below the entries.
Contacts:
LOAD
    "No_" as ContactNo,
    Name,
   "Customer No_" as CustomerNo,
    "Vendor No_" as VendorNo;

SQL SELECT
    "No_",
    Name,
    "Customer No_",
    "Vendor No_"
FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact";
Entries:
LOAD 
"Entry No_",
"Item No_",
"Posting Date",
"Document No_",
  if("Source Type" = '1', "Source No_") as CustomerNo,  
  if("Source Type" = '2', "Source No_") as VendorNo;
   //The problem has something to do with this, if I remove the if-construction and only load the customers, the join works perfectly
SQL SELECT
"Entry No_",
"Item No_",
"Posting Date",
"Document No_",
"Source Type",
"Source No_"
FROM $(@DatabaseName)
.dbo."$(@CompanyName)$Value Entry";


NewTable:
NoConcatenate load * Resident Entries;
join (NewTable) LOAD * Resident Contacts;
Drop table Entries;
Drop table Contacts;
3 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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.

Not applicable
Author

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;