Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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;
Tags (2)
3 Replies
Gysbert_Wassenaar
Not applicable

Re: Problem with join

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

Re: Problem with join

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

Re: Problem with join

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;